sql question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql question

Author

Topic

jeffbond
Starting Member

16 Posts

Posted - 2008-11-29 : 16:06:11

Hi,

I got some sql that returns the following:

june apple 12
july apple 10
august apple 8
august banana 7

instead I want :

june apple 12
june banana null
july apple 10
july banana null
august apple 8
august banana 7

select month,product,avg(price) as price
from mytable
where product in ('banana','apple')
group by month,product

Thx,

Shamil
Starting Member

5 Posts

Posted - 2008-11-29 : 16:56:16

You should replace NULL value:
SELECT month,product,avg(price) as price
FROM (SELECT [month], product,isnull(price,0) as price
FROM mytable) T1
where product in ('banana','apple')
group by month,product

jeffbond
Starting Member

16 Posts

Posted - 2008-11-29 : 17:54:04

this won't work because the banana record does't exist for june.

I do not have any null values in this table

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-29 : 23:17:07

You'll need a table with the products in it. I'll assume you have created one named products, with a productid as the primary key, and used the productid instead of the product name in mytable.

I am also creating a table of months inline in this query, but you probably want to create a months and/or dates table because you can't get a row for june and banana without having june in another table.

Now the query becomes:

select months.month, products.product, avg(price) as price
from products
cross join (select 'june' as month union all
    select 'july' union all
    select 'august') months
left join mytable on mytable.productid = products.productid 
    and mytable.month = months.month
where products.product in ('banana','apple')
group by months.month, products.product

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 01:07:36

quote:
Originally posted by jeffbond

Hi,

I got some sql that returns the following:

june apple 12
july apple 10
august apple 8
august banana 7

instead I want :

june apple 12
june banana null
july apple 10
july banana null
august apple 8
august banana 7

select month,product,avg(price) as price
from mytable
where product in ('banana','apple')
group by month,product

Thx,


SELECT m.month,p.product,
AVG(t.price)
FROM (SELECT DISTINCT product FROM mytable)p
CROSS JOIN (SELECT DISTINCT month FROM mytable)m
LEFT JOIN mytable t
ON t.month=m.month
AND t.product=p.product
GROUP BY m.month,p.product

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-30 : 01:37:25

Note that visakh16's solution will work well as long as every product and every month you want to see has at least one sale. If you have any products or months when no sales were made then that product or month will not appear in the list.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 01:40:31

quote:
Originally posted by snSQL

Note that visakh16's solution will work well as long as every product and every month you want to see has at least one sale. If you have any products or months when no sales were made then that product or month will not appear in the list.

for that you need a new master table with month info from january till december or an inline derived table as you showed earlier but with all months from january till december.

jeffbond
Starting Member

16 Posts

Posted - 2008-11-30 : 11:04:40

thank you both!

That's it! I did not know the cross join trick.

I applied it to my work example (not posted here) and it works a charm.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 11:47:25

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources