SQL SELECT PROBLEM (Aggregates)

[Kinl]

I have a database with a table that holds ecommerce information. There is a Date/Time field in it the table that i need to pull info from. Everytime someone purchases something, a record gets inserted with what itemm, the quantity, and what date they requested it.

What I'm trying to do is basically request the SUM(QUANTITY) for each day for that month, with a query like this one:

SELECT SUM(QUANTITY) as ItemSum, DAY(ReqShipDate) as ShipDay
FROM SOP10200
WHERE ITEMNMBR = '123456'
and MONTH(ReqShipDate) = 11
and YEAR(ReqShipDate) = 2001
GROUP BY QUANTITY, ReqShipDate
ORDER BY ShipDay

But I'm getting multiple entries for one day, I want it to look something like this:

ItemSum ShipDay
---------------- ---------------------
120 1
109 2
23 3
etc... etc


current i'm getting this:

ItemSum ShipDay
-------------- ----------------
10 1
93 1
12 1
34 2
23 2
etc... etc....


ANy help would be appreciated!!

thanx

shorty

 

[Jonax]

Try grouping by ShipDay instead of RegShipDate...

 

[rac2]

Yes and with MS SQL Server it will have to be
GROUP BY DAY(ReqShipDate).

 

[Kinl]

Thank you for yourl Help.

rac2's solution is what solved my problem. Here is my query now:

SELECT DAY(ReqShipDate) as ShipDay, SUM(QUANTITY) as ItemSum
FROM SOP10200
WHERE ITEMNMBR = '123456'
and MONTH(ReqShipDate) = 11
and YEAR(ReqShipDate) = 2001
GROUP BY DAY(ReqShipDate)
ORDER BY ShipDay

Thanx!

shorty