MySQL single query multiple results

Question

I'm trying to grab all the info I want through the following query. That is comment value where status = 0 and SUM points where status = 0 and where status = 1. Here is what I've got until now (I can't grab the comment value at this point)

SELECT
  IF(status = 0, comment, NULL) AS com,
  SUM(IF(status = 0, points, 0)) AS points1,
  SUM(IF(status = 1, points, 0)) AS points2
FROM `tablename`
  WHERE mid = $mid
  AND stage = 0

Table data:

+----+--------+--------+-----------+-----+------+
| id |   mid  | points |  comment  |stage|status|
+----+--------+--------+-----------+-----+------+
|  1 | 500    |   15   | Text here |  0  |   0  |
|  2 | 500    |   5    | Blablabla |  0  |   1  |
|  3 | 20     |   7    |           |  1  |   0  |
|  4 | 356    |   10   | More text |  0  |   2  |
|  5 | 9      |   0    |           |  1  |   0  |
|  6 | 52     |   5    | Text etc  |  0  |   1  |
|  7 | 520    |   13   | Texting   |  1  |   0  |
|  8 | 540    |   8    |           |  0  |   0  |
+----------------------------------------+------+

Results I am looking for:

Where mid = 500 and stage = 0
IF status = 0 get me the points (15 in this case)
IF status = 1 get me the points (5 in this case)
IF status = 0 get me the comment (Text here in this case)

Edit your question and provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Apr 20, 2017 at 2:33

Gordon Linoff · Accepted Answer · 2017-04-20 02:48:53Z

1

If you are using aggregation functions, then all columns should be aggregated. I prefer CASE to IF() because the former is standard SQL:

SELECT GROUP_CONCAT(CASE WHEN status = 0 THEN comment END) AS com,
       SUM(CASE WHEN status = 0 THEN points ELSE 0 END) AS points_0,
       SUM(CASE WHEN status = 1 THEN points ELSE 0 END) AS points_1
FROM `tablename`
WHERE mid = $mid AND stage = 0 ;

Comments:

This uses GROUP_CONCAT() in case more than one row matches the condition and has status = 0.
The names of the "points" columns match the statuses being compared.

answered Apr 20, 2017 at 2:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Bill Over a year ago

If there is only one row that matches the condition for the comment, what should I use instead of GROUP_CONCAT?

Gordon Linoff Over a year ago

@Bill . . . You can use MAX() or MIN().

Collectives™ on Stack Overflow

MySQL single query multiple results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related