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
 General SQL Server Forums
 New to SQL Server Programming
 Counting

Author  Topic 

mpayne2010
Starting Member

14 Posts

Posted - 2013-02-28 : 17:46:34
When I run this sql:

Select count(Prodname) ProdCount, ProdName, year(orderdate) year
from products
inner join orders on prodnum = orderprodnum
group by prodname, orderdate
Order By Prodname, orderdate

Im getting this result:
ProdCount ProdName year
1 Gargantuan Alabaster Delightful Carpet sweeper 2000
1 Gargantuan Alabaster Delightful Carpet sweeper 2000
1 Gargantuan Alabaster Delightful Carpet sweeper 2000
1 Gargantuan Alabaster Delightful Carpet sweeper 2000
1 Gargantuan Alabaster Delightful Carpet sweeper 2001
1 Gargantuan Alabaster Delightful Carpet sweeper 2001
1 Gargantuan Alabaster Delightful Carpet sweeper 2001

The result I want is for each year 2000 to be totaled. So the result would look like:

4 Gargantuan Alabaster Delightful Carpet sweeper 2000
3 Gargantuan Alabaster Delightful Carpet sweeper 2001

Thanks in advance!!!
Michele

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-28 : 18:07:22
Don't group by orderdate, rather by YEAR(OrderDate)
Go to Top of Page

mpayne2010
Starting Member

14 Posts

Posted - 2013-02-28 : 18:13:48
Nope, still giving me the same results
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 18:21:18
I know I am repeating what Lamprey said, but don't group by orderdate, instead group by year(orderdate). That would give you one row per product, per year. The only way that you will still be getting multiple rows is if the prodname is different in each row (perhaps because of hidden characters)
Select count(Prodname) ProdCount, ProdName, year(orderdate) year
from products
inner join orders on prodnum = orderprodnum
group by prodname, year(orderdate)
Order By Prodname, year(orderdate)

Go to Top of Page
   

- Advertisement -