3 tables and a sum() - 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

General SQL Server Forums

New to SQL Server Programming

3 tables and a sum()

Author

Topic

Tim_Blanch
Starting Member

1 Post

Posted - 2012-08-20 : 16:41:28

I have 3 tables
BillHeader with SupplierId, CustomerId
BillProperty with SupplierId
BillChg with CustomerId, CommFlg

with the query:
SELECT DISTINCT CustomerId, SUM(CASE WHEN CommFlg = 1 THEN 1 ELSE 0 END) AS ComFlag FROM BillChg
GROUP BY CustomerId
I get my CustomerId, ComFlag where ComFlag is the sum of all bool fields that are associated with CustomerId.
Now I would like(that I am having trouble with) to add my SupplierId that is associated with the CustomerId.
I have tried:
SELECT DISTINCT a.SupplierId, b.CustomerId, SUM(CASE WHEN c.CommFlg = 1 THEN 1 ELSE 0 END) AS ComFlag
FROM BillProperty a
JOIN BillHdr b ON a.SupplierId = b.SupplierId
JOIN BillChg c ON b.CustomerId = c.CustomerId
WHERE a.SupplierId = 745079813 AND c.CustomerId = 2277565
GROUP BY a.SupplierId, b.CustomerId, c.CommFlg
ORDER BY a.SupplierId
But no luck
I would like an output like:
SupplierId, CustomerId, Sum of CommFlg

Can anyone help me a little here?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-20 : 16:51:33

[code]
SELECT a.SupplierId, b.CustomerId,c.ComFlag
FROM BillProperty a
JOIN BillHdr b ON a.SupplierId = b.SupplierId
JOIN(
SELECT CustomerId, SUM(CASE WHEN CommFlg = 1 THEN 1 ELSE 0 END) AS ComFlag
FROM BillChg
GROUP BY CustomerId
)c
ON b.CustomerId = c.CustomerId
WHERE a.SupplierId = 745079813 AND c.CustomerId = 2277565
ORDER BY a.SupplierId
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources