Multiple Count based on value MYSQL

Question

i have a table that have the following columns and sample data.

(this data is output of a query so i have to use that query in FROM () statement)

Type|  Time              | Count
--------------------------------
1   |2013-05-09 12:00:00 | 71
2   |2013-05-09 12:00:00 | 48
3   |2013-05-09 12:00:00 | 10
3   |2013-05-09 13:00:00 | 4
2   |2013-05-16 13:00:00 | 30
1   |2013-05-16 13:00:00 | 31
1   |2013-05-16 14:00:00 | 4
3   |2013-05-16 14:00:00 | 5

I need to group data based on time so my output should look like this

AlarmType1 | AlarmType2 |AlarmType3| AlarmTime
--------------------------------
71         | 48         | 10       | 2013-05-09 12:00:00
31         | 30         | 4        | 2013-05-09 13:00:00
4          | 0          | 5        | 2013-05-09 14:00:00

i have tried this query

SELECT
SUM(IF (AlarmType = '1',1,0)) as  AlarmType1,
SUM(IF (AlarmType = '2',1,0)) as  AlarmType2,
SUM(IF (AlarmType = '3',1,0)) as  AlarmType3,
AlarmHour
FROM 'Table1'
GROUP BY Time

but this did not work, as i am missing Count in my Query, have to adjust Count im my Query

i found one mistake that i am not using my Count in my query. but is there any way to do so? — Mujahid Daud Khan
– Mujahid Daud Khan, Commented Oct 22, 2013 at 6:40

Michael Sivolobov · Accepted Answer · 2013-10-22 07:15:16Z

2

You need SUM(Count), not SUM(1):

SELECT
SUM(IF (AlarmType = '1',Count,0)) as  AlarmType1,
SUM(IF (AlarmType = '2',Count,0)) as  AlarmType2,
SUM(IF (AlarmType = '3',Count,0)) as  AlarmType3,
AlarmHour
FROM 'Table1'
GROUP BY Time

answered Oct 22, 2013 at 7:15

Michael Sivolobov

13.9k5 gold badges48 silver badges68 bronze badges

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

1 Comment

Mujahid Daud Khan Over a year ago

yes, Count was missing and adjusted, this worked. i am noob to mysql.

M Shahzad Khan · Accepted Answer · 2013-10-22 06:46:57Z

0

SELECT SUM(IF (AlarmType = '1',1,0)) as  AlarmType1,
SUM(IF (AlarmType = '2',1,0)) as  AlarmType2,
SUM(IF (AlarmType = '3',1,0)) as  AlarmType3,
AlarmHour
FROM (your query) as t group by t.Time

answered Oct 22, 2013 at 6:46

M Shahzad Khan

9351 gold badge10 silver badges22 bronze badges

Comments

Antoniossss · Accepted Answer · 2013-10-22 07:12:58Z

0

Your table1 is your subquery right? Well it has to have its alias so it will be something like that

SELECT
SUM(IF (AlarmType = '1',1,0)) as  AlarmType1,
SUM(IF (AlarmType = '2',1,0)) as  AlarmType2,
SUM(IF (AlarmType = '3',1,0)) as  AlarmType3,
AlarmHour
FROM (subquery giving table1) as subTable
GROUP BY subtable.Time

EDIT: As for summing, why dont you just use multiple group by? EG. GROUP BY Type, Time and show type for every group and type SELECT Type,Time,Sum(Count) ...?

OK I get it now what are you trying to achieve. If you want to "add columns" you will have to use JOINS. So create single query for every column you need (every AlarmType count) and join them on commond value whitch is Time of alarm.

edited Oct 22, 2013 at 7:12

answered Oct 22, 2013 at 6:42

Antoniossss

32.7k7 gold badges67 silver badges119 bronze badges

6 Comments

Mujahid Daud Khan Over a year ago

i have tried this approach yesterday, this only shows if the id for a particular date exists or not, i mean just 1 and 0, instead of the count of the alarm, i figured out i have not used my count in my query, so have to adjust the count in the query

Antoniossss Over a year ago

I didn't analized your query, just answered how to handle error reported by you.

Mujahid Daud Khan Over a year ago

if i simply group by Time, ID info will be lost, i don't know much about mysql, so i may be wrong in understating your edit.

Mujahid Daud Khan Over a year ago

i have an option to filter my record in my c# application, but it would be better if i get the data in required format

Antoniossss Over a year ago

Sorry, but what do you mean by "info will be lost"?

|

Collectives™ on Stack Overflow

Multiple Count based on value MYSQL

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related