SQL Server Performance

Could use some help on this one

Discussion in 'SQL Server 2005 General Developer Questions' started by themausman, Aug 2, 2007.

  1. themausman New Member

    Hi,
    I'm stuck with this situation. Adriaan pointed me in the right direction (See post can this be done using CTE) but i'm puzzled with the last piece.
    I have one table with four records. The output i'm looking for is:
    YearRequested AmountTotal TotalNrOrders AmountTotalOrdered TotalnrSubmitted
    2007 88000 4 10000 1

    What I have so far is this:
    YearRequested AmountTotal TotalNrOrders AmountTotalOrdered TotalnrSubmitted
    2007 78000 3 0 0
    2007 10000 1 10000 1

    I'm using the following select statement:
    SELECT DATEPART(yy, DateSubmitted) AS YearRequested, SUM(OrderSum) AS AmountTotal,
    COUNT(*) AS 'TotalNrOrders', SUM(CASE WHEN submitted=1 THEN OrderSum ELSE 0 END) AS AmountTotalOrdered,(SELECT COUNT(*) WHERE submitted=1) AS AantalOpdracht
    FROM Offer
    GROUP BY DATEPART(YY, DateSubmitted), Submitted
    What is the last step I have to take to get the result i'm looking for?
    Sure hope someone can help me on this one.
    Thanks in advance
    Maurice
  2. Adriaan New Member

    You have ...
    GROUP BY DATEPART(YY, DateSubmitted), Submitted
    ... which means you will get one row for each value of Submitted.
    Just drop Submitted from the GROUP BY clause.
  3. themausman New Member

    Hi Adriaan,
    Thanks for taking the time to think on this one with me. I tried your suggestion but then I get an error which states:
    Column 'Offer.submitted' is invalid in the select list because it is not contained in either an aggregate function or the group by clause.
    So I must be missing something
    p.s. as you can see I translated the columns from dutch to english
    Maurice
  4. Adriaan New Member

    Ha die Maurice,
    Check out the final expression in your column list - change that to SUM(Submitted) which should give you the count since true is 1 and false is 0.
  5. themausman New Member

    Ha die Adriaan,
    Still not getting it right. For clarity this is the original statement (in dutch) - I didn't get what I had to change to sum(submitted)
    SELECT DATEPART(yy, DatumAanvraag) AS Aanvraag, SUM(Inschrijfsom) AS Bedrag, COUNT(*) AS 'aantal', SUM(CASE WHEN Opdracht=1 THEN Inschrijfsom ELSE 0 END) AS Bedrag, SELECT(COUNT (*) WHERE Opdracht=1) AS AantalOpdracht
    FROM Offerte
    GROUP BY DATEPART(YY, DatumAanvraag)
    The field Opdracht is a datatype bit. When trying to sum this SQL tells me it can't be done. Should I change the Opdracht datatype to text to get this result. I'm need this output as a source for many reports. So if I can get this 'base' right i'm saved ;-)
    If you need more info just let me know (thanks for being patient with me)
    Maurice
  6. Adriaan New Member

    SELECT DATEPART(yy, DatumAanvraag) AS Aanvraag, SUM(Inschrijfsom) AS Bedrag, COUNT(*) AS 'aantal', SUM(CASE WHEN Opdracht=1 THEN Inschrijfsom ELSE 0 END) AS Bedrag, SUM(CASE WHEN Opdracht=1 THEN 1 ELSE 0 END) AS AantalOpdracht
    FROM Offerte
    GROUP BY DATEPART(YY, DatumAanvraag)
  7. themausman New Member

    Hé Adriaan,
    That did the trick. I can't thank you enough for helping me out on this one your a lifesaver ;-)
    Maurice
    p.s. In the MS newsgroups you can somehow indicate that the given answer is very helpfull. Can this also be done in this forum or do people just have to read the posts to see if the given answers are helpfull.
    thanks again.
  8. Adriaan New Member

    This site has a good tradition of following up on questions and answers. No need for compliments here! ;-)
  9. satya Moderator

    I do second what Adriaan refers and it is nice to see when the originator gets back with such feedback...
  10. themausman New Member

    Thanks for all the replies. I must say that I really appreciate the help. I'm in the middle of learning SQL and working with it for several clients. It's nice to know that there is such a place as this with so much knowledge. I hope I can contribute in the future the way you guys do ;-)
    Maurice
  11. Madhivanan Moderator

    There is no option
    But you can edit the topic and add this at the end
    Orignal subject - (Solved & helpful )

Share This Page