Reputation: 754
I have three tables
City(Id,CityName)
Malls(Id,MallName,CityId)
Stores(Id,StoreName,MallId)
What i want is:
1 = I want to find SUM of all stores in side each city
2= Find SUM of stores in each city if CityId IN(1,2,3,5)
3= Find SUM of stores in each city AS OTHERS if CityId IN(4,6,7,8)
I tried this but can not go further
select count(id) FROM Stores AS s ,Malls AS m,City AS c where s.mallid=m.id and m.cityid=c.id GROUP BY c.id
Upvotes: 0
Views: 58
Reputation: 4402
Here's one query which gives you all 3 answers:
SELECT cityId,
storesPerCity,
SUM (CASE WHEN cityId IN (1,2,3,5) THEN storesPerCity ELSE 0 END) NumStores1_2_3_5,
SUM (CASE WHEN cityId IN (4,6,7,8) THEN storesPerCity ELSE 0 END) Other
FROM (
SELECT c.Id cityId,
COUNT(*) storesPerCity
FROM Stores s INNER JOIN
Malls m ON s.Mallid = m.Id INNER JOIN
City c ON m.CityId = c.Id
GROUP BY c.Id) x
GROUP BY cityId, storesPerCity
Upvotes: 0
Reputation: 15058
Stores count in each city:
SELECT c.CityName, COUNT(c.id) AS StoresInCity
FROM Stores s
INNER JOIN Malls m ON s.mallid = m.id
INNER JOIN City c ON m.cityid = c.id
GROUP BY c.CityName
Stores count in each city where CityId IN(1, 2, 3, 5)
:
SELECT c.CityName, COUNT(c.id) AS StoresInCity
FROM Stores s
INNER JOIN Malls m ON s.mallid = m.id
INNER JOIN City c ON m.cityid = c.id
WHERE m.CityId IN (1, 2, 3, 5)
GROUP BY c.CityName
Stores count in each city where CityId IN(4, 6, 7, 8)
:
SELECT c.CityName, COUNT(c.id) AS StoresInCity
FROM Stores s
INNER JOIN Malls m ON s.mallid = m.id
INNER JOIN City c ON m.cityid = c.id
WHERE m.CityId IN (4, 6, 7, 8)
GROUP BY c.CityName
Upvotes: 1