anujsharma9196 anujsharma9196 - 5 months ago 20
SQL Question

SQL select distinct count and data from table

I have a table like

+----+----------+------------+
| id | order_id | service_id |
+----+----------+------------+
| 1 | 3423343 | 123 |
+----+----------+------------+
| 2 | 3432356 | 124 |
+----+----------+------------+
| 3 | 2345643 | 123 |
+----+----------+------------+
| 4 | 2313343 | 125 |
+----+----------+------------+


What I want to get is the count of each
service_id
which will give output like this

+------------+------+
| service_id | cout |
+------------+------+
| 123 | 2 |
+------------+------+
| 124 | 1 |
+------------+------+
| 125 | 1 |
+------------+------+


and also to arrange it in
Highest -> Lowest
order

I'm working in CakePHP 3.2

Answer

Use group by on column service_id and count the rows of every group.

SELECT 
    service_id, 
    COUNT(service_id) AS `Count` 
FROM 
    tablename -- your table name
GROUP BY 
    service_id
ORDER BY
    `Count` DESC;
Comments