CODEWITHSUNDEEP
mysqljoincount
dr0zd
dr0zd

Reputation: 1378

Mysql Count Several fields

I have a table

id|user_id|start|stop
 1|      1|    1|   1
 2|      2|    1|   1
 3|      2|    1|   0
 4|      3|    0|   0

I want to get next result

user_id|total|start|stop
      1|    1|    1|   1
      2|    2|    2|   1
      3|    1|    0|   0

So i want to make 3 count and group it by user_id ang get. Is it possible to prepare query with Joins and without derived tables?

Upvotes: 2

Views: 116

Answers (3)

Kevin Bedell
Kevin Bedell

Reputation: 13414

The total row as to count rows with 1 in start and 1 in stop twice.

SELECT user_id, 
   count(case start WHEN 1 then 1 else null end) AS start, 
   count(case stop WHEN 1 then 1 else null end)  AS stop, 
   count (*) AS total 
FROM   tablename 
GROUP  BY user_id

Upvotes: 2

eggyal
eggyal

Reputation: 125955

SELECT   user_id, COUNT(*) total, SUM(start=1) start, SUM(stop=1) stop
FROM     my_table
GROUP BY user_id

Upvotes: 3

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

SELECT user_id, 
       Sum (start) AS start, 
       Sum (stop)  AS stop, 
       Count(*)    AS total 
FROM   tablename 
GROUP  BY user_id

Upvotes: 3

Related Questions