| Author |
 Topic  |
|
|
Jjll
Starting Member
3 Posts |
 Posted - 02/24/2013 : 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
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 02/24/2013 : 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 - 02/24/2013 : 20:21:29
|
Tried not working
Thanks for trying
Scratch that it worked! Thanks Jimf |
Edited by - Jjll on 02/24/2013 20:25:45 |
|
|
|
Jjll
Starting Member
3 Posts |
Posted - 02/24/2013 : 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
|
Edited by - Jjll on 02/24/2013 20:36:46 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 02/25/2013 : 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 |
|
|
| |
Topic |
|
Query help, output same field twice different crit
