Advertisement
Guest User

Untitled

a guest
Feb 16th, 2019
121
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.82 KB | None | 0 0
  1. SELECT
  2.     t.Name AS TownName,
  3.     CONVERT(INT,((MCount.MOrders *100) / TCount.TotalCount)) AS MalePercent,
  4.     CONVERT(INT,((FCount.FOrders *100) / TCount.TotalCount)) AS FemalePercent
  5. FROM Towns AS t
  6. LEFT JOIN (SELECT
  7.     o.TownId,
  8.     COUNT(*) AS FOrders
  9. FROM Orders AS o
  10. LEFT JOIN Clients AS c ON c.Id = o.ClientId
  11. WHERE c.Gender = 'F'
  12. GROUP BY o.TownId) AS FCount ON t.Id = FCount.TownId
  13. LEFT JOIN (SELECT
  14.     o.TownId,
  15.     COUNT(*) AS MOrders
  16. FROM Orders AS o
  17. LEFT JOIN Clients AS c ON c.Id = o.ClientId
  18. WHERE c.Gender = 'M'
  19. GROUP BY o.TownId) AS MCount ON MCount.TownId = t.Id
  20. LEFT JOIN (SELECT
  21.     o.TownId,
  22.     COUNT(*) AS TotalCount
  23. FROM Orders AS o
  24. LEFT JOIN Towns AS t ON o.TownId = t.Id
  25. GROUP BY o.TownId) AS TCount ON TCount.TownId = t.Id
  26. GROUP BY t.Name,MCount.MOrders,TCount.TotalCount,FCount.FOrders,t.Id
  27. ORDER BY t.Name,t.Id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement