MySQL joining multiple sql results when all data is in one table

Question

I have a table containing lots of data with several fields including:

day (Monday ~ Sunday)
City (NY, LA, FL, etc.)

I would like to use php to make a table to display totals like this:

  --- Mon --- Tue --- Wed ...
NY    6       8       9
LA    7       5       3
FL    1       0       4 

But I am having trouble to write the SQL to make this work. I have used union but that appends the result at the bottom, so it seems I need to use inner join, or not even it. The two queries that I can get remotely close to what I want are:

select city, count(id) as 'Monday' from index where city = "NY" and day = 'Monday' group by city union all
select city, count(id) as 'Monday' from index where city = 'LA' and day = 'Monday' group by city union all
select city, count(id) as 'Monday' from index where city = 'FL' and day = 'Monday' group by city

This produces the following result

  --- Mon
NY    6
LA     7
FL     1

The problem with this is I don't know how to add the rest of the days to the right of it

Another query I have tried is
select a.day, a.counts as 'NY', b.counts as 'LA' from
(select day, count(id) as counts from index where city='NY' GROUP BY DAY) as a
left join
(select day, count(id) as counts from index where city='LA' GROUP BY DAY) as b
on (a.day = b.day)
order by field (a.day, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')

This produces the following result

                 NY --- LA ---
Monday      6        7
Tuesday     8        5

And the problem with this one is the row and column should be swapped, also I don't know how to add another city (I have many cities).

All the data is in one table called "index". Could someone give me the guidance on how to write the SQL? I would also need the field to show 0 or Null when there is no record. The end goal is to produce the table with PHP.

Thank you for taking the time to read and answer.

Answer 1

It seem's like you want to pivot the data by the day and group it by City. If this then you can try the following query may this help.

select city, 
sum(case when (day = 'Monday') then  1 else 0 end)  'Monday',
sum(case when (day = 'Tuesday') then  1 else 0 end)  'Tuesday',
sum(case when (day = 'Wednesday') then  1 else 0 end)  'Wednesday',
sum(case when (day = 'Thursday') then  1 else 0 end)  'Thursday',
sum(case when (day = 'Friday') then  1 else 0 end)  'Friday',
sum(case when (day = 'Saturday') then  1 else 0 end)  'Saturday',
 sum(case when (day = 'Sunday') then  1 else 0 end)  'Sunday'

from `index` group by city

Answer 2

I don't have your schema so I'm taking a few liberties. You probably want something like this:

Ps: Depending on your db you may be able to replace the sum(case...) with the proper sumif function

SELECT 
  city,
  sum(case when day = 'Monday' then 1 else 0 end) AS 'Monday',  
  sum(case when day = 'Tuesday' then 1 else 0 end) AS 'Tuesday',  
  sum(case when day = 'Wednesday' then 1 else 0 end) AS 'Wednesday',  
  sum(case when day = 'Thursday' then 1 else 0 end) AS 'Thursday',  
  sum(case when day = 'Friday' then 1 else 0 end) AS 'Friday',  
  sum(case when day = 'Saturday' then 1 else 0 end) AS 'Saturday',  
  sum(case when day = 'Sunday' then 1 else 0 end) AS 'Sunday'
FROM your_table
GROUP BY city

Let me know if it helps