Query help, output same field twice different crit

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

Query help, output same field twice different crit

Author

Topic

Jjll
Starting Member

3 Posts

Posted - 2013-02-24 : 20:05:37

1. For every supplier, display supplier id and count the number of products supplied
2. Modify part (1) of query to display all Suppliers and in in separate column suppliers from USA

part 1 works
Select sp.supplierid,
count (p.productid)
From .suppliers sp
join corp.products p on (sp.supplierid = p.supplierid)
group by (sp.supplierid)
;
I cant figure out how to do part 2 help please

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-24 : 20:14:34

The second part is a bit vague, but something like
SELECT sp.supplierid
,count(p.productid) as suppProdCount
,SUM(CASE WHEN sp.supplierID = 'USA'
THEN 1
ELSE 0
END
) as USASuppliers
FROM suppliers sp
join corp.products p on (sp.supplierid = p.supplierid)
group by (sp.supplierid)

Jim

Everyday I learn something that somebody else already knew

Jjll
Starting Member

3 Posts

Posted - 2013-02-24 : 20:21:29

Tried not working

Thanks for trying
Scratch that it worked! Thanks Jimf

Jjll
Starting Member

3 Posts

Posted - 2013-02-24 : 20:29:30

Is there a way to display the supplier name instead of 1

quote:
Originally posted by jimf

The second part is a bit vague, but something like
SELECT sp.supplierid
,count(p.productid) as suppProdCount
,SUM(CASE WHEN sp.supplierID = 'USA'
THEN 1
ELSE 0
END
) as USASuppliers
FROM suppliers sp
join corp.products p on (sp.supplierid = p.supplierid)
group by (sp.supplierid)

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-25 : 06:53:47

If the SupplierName lives in the suppliers table, then just add that to the select

SELECT sp.supplierName
,count(p.productid) as suppProdCount
,SUM(CASE WHEN sp.supplierID = 'USA'
THEN 1
ELSE 0
END
) as USASuppliers
FROM suppliers sp
join corp.products p on (sp.supplierid = p.supplierid)
group by (sp.supplierName)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources