Getting formatted and sorted data from SQL

Question

I would like to get data from SQL for a graph. Instead of getting all the data and sorting in PHP I would like to solve this using SQL.

The Dates should be grouped by day and platform should be counted and sorted to ios and android.

BONUS: if you can sort the platform by all values, rather than given values, this is even better.

Here is the data presented in SQL:

date                |platform
--------------------+----------
2014-04-22 11:15:55 |ios
2014-04-22 12:15:55 |android
2014-04-22 13:15:55 |ios
2014-04-23 11:15:55 |ios
2014-04-23 12:15:55 |android
2014-04-23 13:15:55 |android

Desired output:

date        |ios    |android
------------+-------+-----
2014-04-22  |2      |1
2014-04-23  |1      |2

the confusion was when combining two types of sorting and grouping — Tarmo Saluste
– Tarmo Saluste, Commented Apr 25, 2014 at 9:05

Noismaster · Accepted Answer · 2014-04-25 09:00:19Z

3

SELECT `date`,
       sum(`ios`) AS `ios`,
       sum(`android`) AS `android`
FROM
  (SELECT DATE(`date`) AS `date`,
          CASE
              WHEN `platform`='ios' THEN 1
              ELSE 0
          END AS `ios`,
          CASE
              WHEN `platform`='android' THEN 1
              ELSE 0
          END AS `android`
   FROM demo) t
GROUP BY `date`

answered Apr 25, 2014 at 9:00

Noismaster

462 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting formatted and sorted data from SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related