Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to return grouped results with zero count in MS SQL

Posted on 2008-06-13
14
Medium Priority
?
511 Views
Last Modified: 2008-06-14
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
Comment
Question by:georgep7
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21784494
can you show the table design, please?
this will be implemented using a LEFT JOIN...
0
 
LVL 2

Expert Comment

by:osiara
ID: 21784502

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

Open in new window

0
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 21784595
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:georgep7
ID: 21784710
Hi angelIII, here is the table design:

Column Name / Data Type / Length:

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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21784722
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
 

Author Comment

by:georgep7
ID: 21784832
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21784833
>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
 

Author Comment

by:georgep7
ID: 21784836
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
 
LVL 2

Expert Comment

by:osiara
ID: 21784838

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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21784845
>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
 

Author Comment

by:georgep7
ID: 21784847
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
 

Author Comment

by:georgep7
ID: 21784849
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
 
LVL 2

Accepted Solution

by:
osiara earned 2000 total points
ID: 21784860
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
 

Author Comment

by:georgep7
ID: 21784868
Excellent - thanks osiara - I've combined the two and can get both count and sales sum for all of them - cheers!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question