GROUP BY problems...

Take a look at the following SELECT command:

Code Snippet

SELECT aagfakt.kndnr1,
aagfaktpos.artnr1,
CONVERT (char, aagfaktpos.faktdatum, 104) AS [Datum],
SUM (CASE
WHEN aagfaktpos.storno = 1
THEN aagfaktpos.bestellmenge * -1
ELSE aagfaktpos.bestellmenge
END) AS absatz
FROM aagfaktpos
INNER JOIN aagfakt
ON aagfaktpos.lfdfaktnr = aagfakt.lfdfaktnr
GROUP BY aagfaktpos.artnr1,
kndnr1,
aagfaktpos.faktdatum
ORDER BY kndnr1 ASC;

The data doesn't group, I don't know why... any help would be appreciated!

My suggestion:

Code Snippet

SELECT aagfakt.kndnr1,
aagfaktpos.artnr1,
CONVERT (char, aagfaktpos.faktdatum, 104) AS [Datum],
SUM (CASE
WHEN aagfaktpos.storno = 1
THEN aagfaktpos.bestellmenge * -1
ELSE aagfaktpos.bestellmenge
END) AS absatz
FROM aagfaktpos
INNER JOIN aagfakt
ON aagfaktpos.lfdfaktnr = aagfakt.lfdfaktnr
GROUP BY aagfaktpos.artnr1,
aagfakt.kndnr1,
CONVERT (char, aagfaktpos.faktdatum, 104)
ORDER BY aagfakt.kndnr1 ASC;

GROUP BY DATEPART issues...

Hi,

I'm trying:

Code Snippet

SELECT SUM(price), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

and, while this executes w/o a problem, I am a bit concerned with my results...

i get a SUM(price) = 2140.21 when running the above SQL for 11/2006

however, when i try:

Code Snippet

SELECT SUM(price)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i get a SUM(price) = 1950.45

if i bump the second date up by one day (i.e. '12/01/2006') i get a SUM(price) = 2140.21, the same value as when I used GROUP BY

any help would be greatly appreciated!

Hi,

What is the result of this query

Code Snippet

SELECT SUM(price), month(order_date), year(order_date)

FROM orders

GROUP BY month(order_date), year(order_date)

HAVING month(order_date)=6 AND year(order_date)=2006

If result is 1950.45 then

u try to use like this syntax

else

check your data one by one ..

|||

Thank you for the response.

I have tried

Code Snippet

...

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and received 2140.21

|||

Is the order_date always truncated at midnight? Try replacing "SUM(price)" with "COUNT(*)" with your queries. Do you get the same record counts for November 2006?

Thanks,
Bryan Smith

|||

I'm not sure about order_date being truncated at midnight... order_date is a datetime, so i am assuming that up until 11:59:59.99PM on 11/30, the date parts I am after remain the same.

I ran the COUNT(*) as suggested and get 804 using:

Code Snippet

SELECT COUNT(*)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

and get 838 when using:

Code Snippet

SELECT COUNT(*), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

the number remains 838 when I add:

Code Snippet

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and drops to 804 if i add:

Code Snippet

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i am baffled, but then again, i'm no expert

thanks for the assistance!

|||

Cool! I think we're on the right track here.

Your original code used "WHERE order_date BETWEEN '11/01/2006' and '11/30/2006'". That means orders created between midnight Nov 1 2006 and midnight at the top of Nov 30 2006. You're dropping orders that occurred from 11/30/2006 12:00:00.001 AM to 11/30/2006 11:59:59.997 PM. That's why the BETWEEN statement gives you 804 records while the DATEPART statement gives you 838 records.

(Please note, SQL Server is only accurate to 3 ms when a datetime data type is used. Times of 11:59:59.998 PM and 11:59:59.999 PM are recorded as 12:00:00.000 AM the next day.)

If you re-write your query to use "WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006 11:59:59.997 PM'" you should get 838 records and your SUM should match the one in the DATEPART query.

Good luck,
Bryan

|||

Ahhh... that makes sense...

would it be correct to assume that the GROUP BY DATEPART... query accurately sums up each months data?

thanks again for your help!

|||

It would. It calculates the month of the date without regard for time, so any orders placed at anytime on 11/30/2006 would fall into November.

B.

Group By Count * >1?

Can this be used to prevent the repetition of records displayed in a page?

Code Snippet

SELECT T_ProgramGuests, GuestName
FROM T_ProgramGuests
GROUP BY ProgramID, GuestName
HAVING (COUNT(*) > 1)

I'm trying to prevent names being repeated. I only want the name to show once followed by the next name and so on. But only once.

Does this do what you want?

SELECT GuestName, MAX(ProgramID) AS pid

FROM T_ProgramGuests

GROUP BY GuestName

ORDER BY GuestName ASC

The above prints the last programId/guest name pair in your table. If you want the first, you can replace the max with min.

Hope this helps!

John (MSFT)