Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

How to return grouped results with zero count in MS SQL

Hi everyone, I have a table in SQL Server 2000 with the following items and when they were sold (the table columns are simply itemname/datesold/pricesold:

ItemA 01/01/2007 $200
ItemA 01/01/2007 $300
ItemB 01/01/2008 $200
ItemB 01/01/2008 $200

I wish to group by item name, and return the total number sold for a specific year. How do I do this so that the query also returns those items with zero count. So for example if I wanted to know how many items were sold in 2007, it should return:

ItemA    2
ItemB    0

For 2008 it should return

ItemA    0
ItemB    2

Instead all I get is a result only for ItemA when I do 2007 sales and results only for ItemB when I do 2008 sales.
0
georgep7
Asked:
georgep7
  • 6
  • 4
  • 3
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the table design, please?
this will be implemented using a LEFT JOIN...
0
 
osiaraCommented:

SELECT items, SUM(CASE WHEN YEAR(date) = 2008 THEN 1 ELSE 0 END)  
FROM table 
GROUP BY items

Open in new window

0
 
joriszwaenepoelCommented:
I would say that your database design is not correct:
- you should have a table "products" with for each product a "ProductID" and "ProductName" column
- you should have a table "sales with for each sale a "ID", a "ProductID", a "Date" and a "Price" column.  The ID should have no meaning, other then being a unique value for each record.  Otherwise you will have some problems when  a product is sold more than once on the same date.

If you have that kind of design, it will be a lot easier to get the result you want, using an left outer join between the "products" and the "sales" tables.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
georgep7Author Commented:
Hi angelIII, here is the table design:

Column Name / Data Type / Length:

ItemSerialNumber / nvarchar / 50
DateSold / smalldatetime / 4
PriceSold / money / 8

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you don't have a separate table with the ItemSerialNumbers? :)

let me assume that the above table is "sales", and the other table is called "items"
select i.itemserialnumber
  , i.itemname
  , sum(case when s.itemserialnumber is null then 0 else 1 end) count_sales_2007
from items i
left join sales s
  on s.itemserialnumber = i.itemserialnumber
 and s.datesold >= convert(datetime, '2007-01-01', 120)
 and s.datesold <  convert(datetime, '2008-01-01', 120)
group by i.itemserialnumber, i.itemname

Open in new window

0
 
georgep7Author Commented:
I'm a little confused - where does itemname come from? Also when I try and execute this I get "The Query Designer does not support the CASE SQL construct"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>where does itemname come from?
as I said, I assume you do have a table with the items name? that is the table "items" in the query...

>"The Query Designer does not support the CASE SQL construct"
you will have to do this without the designer (a tool I don't like at all, btw), a plain query window will have to do...
0
 
georgep7Author Commented:
I don't have a table with the items name - the reason is that the column with the ItemSerialNumber is pretty much an ID number.

0
 
osiaraCommented:

SELECT ItemSerialNumber , SUM(CASE WHEN YEAR(DateSold ) = 2008 THEN 1 ELSE 0 END)  
FROM table 
GROUP BY ItemSerialNumber 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I don't have a table with the items name - the reason is that the column with the ItemSerialNumber is pretty much an ID number.

well, that sounds to be like a contradiction.
if the SerialNumber is an ID, you must have a table having the list?
otherwise, in the sales table, if an item is not sold yet, how could you look it up?

in layman's terms: where does the ItemSerialNumber come from?


if really there is no such table...
 
select i.itemserialnumber
  , sum(case when s.itemserialnumber is null then 0 else 1 end) count_sales_2007
from ( select itemserialnumber from sales group by itemserialnumber ) i
left join sales s
  on s.itemserialnumber = i.itemserialnumber
 and s.datesold >= convert(datetime, '2007-01-01', 120)
 and s.datesold <  convert(datetime, '2008-01-01', 120)
group by i.itemserialnumber 

Open in new window

0
 
georgep7Author Commented:
osiara, that works perfectly - how do I do the sum of the prices instead of just a count? And can I send all this to a new table?
0
 
georgep7Author Commented:
angelIII, the serial number is a combination of numbers and dashes - each number correspondents to a set of details from 10 different tables, which together describe a product's specifics.
0
 
osiaraCommented:
Maybe something lik this :
SELECT ItemSerialNumber, SUM(CASE WHEN YEAR(DateSold ) = 2008 THEN PriceSold ELSE 0 END) AS SumOfPrices 
INTO new_table
FROM table 
GROUP BY ItemSerialNumber 

Open in new window

0
 
georgep7Author Commented:
Excellent - thanks osiara - I've combined the two and can get both count and sales sum for all of them - cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now