Solved

How to return grouped results with zero count in MS SQL

Posted on 2008-06-13
14
507 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
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…

734 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