total amounts per product per year? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

total amounts per product per year?

Author

Topic

LordAzus
Starting Member

3 Posts

Posted - 2011-08-23 : 09:37:36

Greetings,

I can't figure out a query to show the totals of all product amounts by date per product.

The setup is simple:
a finance table with 3 columns: year, pid and and amount
a product table with 2 columns: pid, name

finance
--------
2009 3 5
2010 3 10
2011 3 15
2009 4 45
2011 4 35

product
-------
3 test3
4 test4

What I want to accomplish with a query is the following output:

2009 3 5
2009 4 45
2010 3 10
2010 4 NULL
2011 3 15
2011 4 35

This is the code I have so far:


select 
    f.year,
    f.pid
from
    (select distinct YEAR from finance) as y
    left outer join finance f on y.year = f.year
order by 
    f.year, f.pid;

This attempt fails to include the line with NULL, because there is no product amount in 2010 and the left outer join doesn't include what isn't there.

The

select distinct year from finance

gives me all the years that should be included in the output.

But I can't seem to figure out how to get the NULL line in there.
Does anybody here have an idea about what I should do?

Thanks,
LordAzus

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 09:56:34

you need to cross join it with pid and result should be left joined with your table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LordAzus
Starting Member

3 Posts

Posted - 2011-08-23 : 10:22:01

Thanks for pointing me in the right direction.
I found a solution:


select 
    coalesce(f.year, y.year),
    coalesce(f.pid, y.pid),
    f.amt
from
(   
    (
        select 
            *
        from
            (select distinct YEAR from finance) as y
            cross join product
     ) as y
     left outer join finance f on y.year = f.year and y.pid = f.pid
)

This gives me the required result:


2009	3	5.00
2009	4	45.00
2010	3	10.00
2010	4	NULL
2011	3	15.00
2011	4	35.00

Cross joins are something I haven't used before. And the resulting query I found seems to be a bit overcomplicated.
Is there perhaps a better way to write it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 11:16:18

why the coalesce? you can directly select from y which will always have the values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LordAzus
Starting Member

3 Posts

Posted - 2011-08-23 : 12:28:47

Oh yeah... you are correct. I didn't see that when I made it earlier. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 12:58:46

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources