Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group By

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2002-03-09 : 22:04:41

Hi, I am trying to group all the items between a date range, here is the SQL I used which do not give me what I want :

SELECT tbl_invoice.itemcode, Sum(tbl_invoice.Sales) AS SumOfSales, tbl_item.itemname
FROM tbl_invoice LEFT JOIN tbl_item ON tbl_invoice.itemcode = tbl_item.itemcode
GROUP BY tbl_invoice.itemcode, tbl_item.itemname, tbl_invoice.Date
HAVING (((tbl_invoice.Date) Between #1/1/2002# And #5/5/2002#));

it displays :
A1,$100,Apple
A1,$200,Apple
P1,$300,PineApple
P1,$400,PineApple

what I want is :
A1,$300,Apple
P1,$700,PineApple

Please help, Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-09 : 22:57:23

SELECT tbl_invoice.itemcode, Sum(tbl_invoice.Sales) AS SumOfSales, tbl_item.itemname
FROM tbl_invoice LEFT JOIN tbl_item ON tbl_invoice.itemcode = tbl_item.itemcode
WHERE tbl_invoice.Date Between #1/1/2002# And #5/5/2002#
GROUP BY tbl_invoice.itemcode, tbl_item.itemname

If this is not MS Access, then you need to use ' instead of # to delimit date values:

SELECT tbl_invoice.itemcode, Sum(tbl_invoice.Sales) AS SumOfSales, tbl_item.itemname
FROM tbl_invoice LEFT JOIN tbl_item ON tbl_invoice.itemcode = tbl_item.itemcode
WHERE tbl_invoice.Date Between '1/1/2002' And '5/5/2002'
GROUP BY tbl_invoice.itemcode, tbl_item.itemname

Edited by - robvolk on 03/09/2002 23:00:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources