SQL query with join, sum, group by, etc

Question

Hi,

I'm trying to build a report that will look like this:

      jan feb mar apr may jun jul ago sep oct nov dec
food   0   1   1   2   0   0   3   1   0   0   1   1
car    1   0   0   0   1   2   1   0   1   2   3   4
home   0   0   1   2   2   2   5   1   2   4   0   0
other  0   0   0   0   0   0   0   0   0   0   0   0

I have two tables: t_item and t_value. t_item has 2 columns: itemID and itemName. t_value has 3 columns: itemID, value, date.

With the following query I can generate a list with all the itens, even with the empty ones.

SELECT t_item.itemID, ISNULL(SUM(t_value.value), 0) AS value
FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
GROUP BY t_item.itemID

But, if I try to include a MONTH column (as follows) the result will show only the items with values...

SELECT t_item.itemID, ISNULL(SUM(t_value.value), 0) AS value, MONTH(date) AS date
FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
GROUP BY t_item.itemID, MONTH(date)

Is it possible to do it? How do I include into the results the itens with no values and group then by month?

TIA,

Bob

Answer 1

For the "holes" in your data you need a filler table. Join this table with a full outer join to the fact table on month.

month
------
month --values jan through dec

For the formating you have a couple options.

• In your reporting tool use the cross tab or matrix function.
• In SQL use the CASE function.
• In SQL use the Pivot function.

Answer 2

Are you using a reporting tool with crosstab like ability?

If not, you can create a sum column for each month. so your resultset would actually look like that report sample.

SELECT t_item.itemID, 

--ISNULL(SUM(t_value.value), 0) AS value, 

sum(case when MONTH(date) = 1 then t_value.value else 0 end) AS m1_sum,
sum(case when MONTH(date) = 2 then t_value.value else 0 end) AS m2_sum,
sum(case when MONTH(date) = 3 then t_value.value else 0 end) AS m3_sum,
--etc

FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
GROUP BY t_item.itemID

Answer 3

Here's an example:

create table #months (value int, name varchar(12))
create table #items (value int, name varchar(24))
create table #sales (month int, item int, sales int)

insert into #months values (1, 'jan')
insert into #months values (2, 'feb')
insert into #months values (3, 'mar')

insert into #items values (1, 'apple')
insert into #items values (2, 'pear')
insert into #items values (3, 'nut')

insert into #sales values (1,1,12)
insert into #sales values (2,2,3)
insert into #sales values (2,2,5)
insert into #sales values (3,3,7)

You can query it using a PIVOT table, like:

select *
from (
    select
     item = #items.name
    , month = #months.name
    , sales = isnull(sum(#sales.sales),0)
    from #months
    cross join #items
    left join #sales on #months.value = #sales.month 
         and #items.value = #sales.item
    group by #months.name, #items.name
) vw
pivot (sum(sales) for month in ([jan],[feb],[mar])) as PivotTable

Or as an alternative, a regular query:

select
    item = #items.name
,   jan = sum(case when #sales.month = 1 then sales else 0 end)
,   feb = sum(case when #sales.month = 2 then sales else 0 end)
,   mar = sum(case when #sales.month = 3 then sales else 0 end)
from #items
left join #sales on #items.value = #sales.item
group by #items.name

Both result in:

item        jan     feb     mar
apple       12      0       0
nut         0       0       7
pear        0       8       0

In the first example, the "cross join" ensures all months and values are present. They're then "left joined", so even the rows with no values are displayed.

The IsNull() is just so that it displays 0 instead of NULL for a month in which that particular item was not sold.

Answer 4

WITH    calendar(mon) AS
     (
     SELECT 1
     UNION ALL
     SELECT mon + 1
     FROM calendar
     WHERE mon < 12
     )
SELECT  itemID, mon, SUM(value)
FROM    calendar c, t_item i
LEFT OUTER JOIN
     t_value v
ON   v.itemID = i.itemID
     AND MONTH(date) = mon
GROUP BY
     i.itemID, mon