Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- t.Name AS TownName,
- CONVERT(INT,((MCount.MOrders *100) / TCount.TotalCount)) AS MalePercent,
- CONVERT(INT,((FCount.FOrders *100) / TCount.TotalCount)) AS FemalePercent
- FROM Towns AS t
- LEFT JOIN (SELECT
- o.TownId,
- COUNT(*) AS FOrders
- FROM Orders AS o
- LEFT JOIN Clients AS c ON c.Id = o.ClientId
- WHERE c.Gender = 'F'
- GROUP BY o.TownId) AS FCount ON t.Id = FCount.TownId
- LEFT JOIN (SELECT
- o.TownId,
- COUNT(*) AS MOrders
- FROM Orders AS o
- LEFT JOIN Clients AS c ON c.Id = o.ClientId
- WHERE c.Gender = 'M'
- GROUP BY o.TownId) AS MCount ON MCount.TownId = t.Id
- LEFT JOIN (SELECT
- o.TownId,
- COUNT(*) AS TotalCount
- FROM Orders AS o
- LEFT JOIN Towns AS t ON o.TownId = t.Id
- GROUP BY o.TownId) AS TCount ON TCount.TownId = t.Id
- GROUP BY t.Name,MCount.MOrders,TCount.TotalCount,FCount.FOrders,t.Id
- ORDER BY t.Name,t.Id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement