Solved

How to return grouped results with zero count in MS SQL

Posted on 2008-06-13
14
508 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

626 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