Solved

How to return grouped results with zero count in MS SQL

Posted on 2008-06-13
14
504 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 142

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 142

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 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