Could use some help on this one

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

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.

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

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.

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

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)

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.

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

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