Query with double group by?

Question

I'm trying to edit this query:

SELECT (WEEKDAY( date ) +1) dayofweek, COUNT( * ) count 
FROM abanners 
WHERE iid = ? AND banner LIKE ? 
GROUP BY (WEEKDAY( date ) +1) 
ORDER BY count DESC

With this query I get the number of entries splited by day of week, but what if I need to get the same thing splitted also by banner?

So I want to have:

 banner1: entries splitted by day of week
 banner2: entries splitted by day of week

 ex: {"banner1" : "1,3,4,1,3,5,1", "banner2": "1,3,4,1,3,5,1"}

How can I do?

Answer 1

To accomplish this, you only need to add banner to both the SELECT list and to the GROUP BY. However, it must come before the dayofweek expression in the GROUP BY.

SELECT 
  banner,
  (WEEKDAY( date ) +1) dayofweek,
  COUNT(*) count
FROM abanners
WHERE 
  iid = ? 
  AND banner LIKE ?
GROUP BY
  banner,
  dayofweek
ORDER BY count DESC

Note that I have used the alias dayofweek in the GROUP BY here. MySQL permits aliases in the GROUP BY where some other RDBMS will report an error there.