2

The following mySQL query gets data from 2 tables, alerts_data and alerts_list. The first table has the data of an alert, and the second has the description of the alert. So in the alerts_data there are multiple rows with the same alerts_data_id that is the same with the alerts_id of alerts_list.

What i want to achieve, is to display something like this

alert number 51, 5 clicked , 2 closed
alert number 57, 13 clicked, 3 closed, 8 waiting

using mySQL or PHP (i do not know if i can get this through plain mySQL)

So for now with my knowledge I can not display the data of alert 51 in one row, but because of the different alerts_data_status i have to show 3 rows for each.

How can I do it as above?

SELECT COUNT( alerts_data_id ) AS total, alerts_data_id, alerts_data_status, alerts_list.alerts_title
FROM  alerts_data 
JOIN alerts_list ON 
alerts_data.alerts_data_id = alerts_list.alerts_id
GROUP BY alerts_data_id, alerts_data_status

//output

total - alerts_data_id - alerts_data_status - alerts_title
5     -     51 - clicked - alert number 51
2     -     52 - closed - alert number 51
13    -     57 - clicked - alert number 57
3     -     57 - waiting - alert number 57
8     -     57 waiting - alert number 57

Note: the alerts number are just examples, it can be any number

// alert_data

id - alerts_data_id - alerts_data_status

// alerts_list

alerts_id - alerts_name - alerts_text

Here's a sqlfiddle: http://sqlfiddle.com/#!9/c70c2/1

O. Jones
  • 103,626
  • 17
  • 118
  • 172
EnexoOnoma
  • 8,454
  • 18
  • 94
  • 179

3 Answers3

1

This may be an application for GROUP_CONCAT().

You first want a summary of your alerts by alerts_data_id and alerts_data_status. This is a little complex, because your sqlfiddle has a whole bunch of empty alerts_data_status strings. Here, I'm replacing those empty strings with `?'. (http://sqlfiddle.com/#!9/c70c2/23/0)

                      SELECT COUNT(*) AS alerts_count, 
                             alerts_data_id, 
                             CASE WHEN LENGTH(alerts_data_status) = 0 THEN '?'
                                  ELSE alerts_data_status END  AS alerts_data_status
                        FROM alerts_data
                       GROUP BY alerts_data_id, alerts_data_status

You then want to roll that up inside another query

SELECT SUM(a.alerts_count) total,
       a.alerts_data_id, b. alerts_name,
       GROUP_CONCAT( CONCAT(a.alerts_count, ': ', a.alerts_data_status)
                     ORDER BY a.alerts_data_status
                     SEPARATOR "; " ) detail
  FROM (
                      SELECT COUNT(*) AS alerts_count, 
                             alerts_data_id, 
                             CASE WHEN LENGTH(alerts_data_status) = 0 THEN '?'
                                  ELSE alerts_data_status END  AS alerts_data_status
                        FROM alerts_data
                       GROUP BY alerts_data_id, alerts_data_status
       ) a
  JOIN alerts_list b ON a.alerts_data_id = b.alerts_id
 GROUP BY a.alerts_data_id, b.alerts_name

This will give you one row for each distinct alerts_data_id. Each alert is identified by its count, its id, and its name. (http://sqlfiddle.com/#!9/c70c2/26/0)

Then the row will contain a semicolon-separated list of the counts of the different alert status.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • hi this is really briliant, thank you! I edited your code to this http://sqlfiddle.com/#!9/c7d832/11 so to instead of having blanks, to have a waiting status. my question is can i have one column for each status instead one for all? – EnexoOnoma Apr 17 '16 at 21:16
  • The jargon for the operation of turning rows into columns is called *pivoting* the table. It's a notorious pain in the neck in MySQL. siax's answer is the right approach. – O. Jones Apr 18 '16 at 01:11
0

If i understand you well i think here is what you need;

SELECT 
  COUNT( alerts_data.id ) AS total, 
  ad.id,
  (SELECT count(*) from alert_list al where al.alerts_id=ad.id and alerts_data_status='clicked') as clicked,
  (SELECT count(*) from alert_list al where al.alerts_id=ad.id and alerts_data_status='closed') as closed,
  (SELECT count(*) from alert_list al where al.alerts_id=ad.id and alerts_data_status='waiting') as waiting,
FROM  alerts_data ad
GROUP BY ad.id
merdincz
  • 427
  • 4
  • 16
  • Thank you, i get a lot of errors though. i am trying to fix them so as to pass the phpmyadmin – EnexoOnoma Apr 17 '16 at 20:35
  • If you can share your table with sample data. I gues i can give you right query. – merdincz Apr 17 '16 at 20:36
  • hey again, please find the schema and everything here http://sqlfiddle.com/#!9/c70c2/1 – EnexoOnoma Apr 17 '16 at 20:44
  • I'm not sure exactly output columns but i guest http://sqlfiddle.com/#!9/c70c2/11 will be fine for you or you can change column and text fields. – merdincz Apr 17 '16 at 20:53
  • can you give me more detail about your output ? Your output which you need "alert number 51, 5 clicked 5, 2 closed 2" I guest alert number 51 part okey isn't it ? So what's 5 mean before the clicked also after? – merdincz Apr 17 '16 at 20:57
  • i had a small typo in my question. so what i want is to have – EnexoOnoma Apr 17 '16 at 20:58
  • in 1 row: the name of the alert and next to it, in 1 column how many times clicked, in 2nd column how many closed and 3rd column how many waiting – EnexoOnoma Apr 17 '16 at 20:59
  • I've changed like that http://sqlfiddle.com/#!9/c70c2/18 On you question you says "closed" but on your sql data haven't got any status like closed if you change the name with closed you can it. – merdincz Apr 17 '16 at 21:01
  • hey man i appreciate your help but come on... i added the blanks with `waiting` here http://sqlfiddle.com/#!9/c7d832/1 and still i dont get the correct counts – EnexoOnoma Apr 17 '16 at 21:07
0

Was checking other answers and your SQLFIDDLE and thought this might be a nicer approach: 

SELECT alerts_list.alerts_title,
  SUM(CASE WHEN alerts_data_status = 'clicked' THEN 1 ELSE 0 END) AS clicked,
  SUM(CASE WHEN alerts_data_status = 'closed' THEN 1 ELSE 0 END) AS closed,
  SUM(CASE WHEN alerts_data_status = 'waiting' THEN 1 ELSE 0 END) AS waiting
FROM alerts_data 
JOIN alerts_list ON alerts_data.alerts_data_id = alerts_list.alerts_id
GROUP BY alerts_list.alerts_title
siax
  • 150
  • 7