Good day, please tell me on this issue:

There is a table of the form:

+-----------------------+ |id|user_id|point|status| ------------------------- |1 | 1 | 3 | 0 | ------------------------- |2 | 1 | 3 | 0 | ------------------------- |3 | 2 | 1 | 0 | ------------------------- |4 | 2 | 0 | 0 | ------------------------- |5 | 3 | 3 | 0 | ------------------------- |6 | 3 | 1 | 0 | ------------------------- |7 | 1 | 0 | 1 | +-----------------------+

What should be the SQL query to get a table of the form:

 +-----------------------------------+ |user_id|total_point|point_3|point_1| ------------------------------------- | 1 | 6 | 2 | 0 | ------------------------------------- | 3 | 4 | 1 | 1 | ------------------------------------- | 2 | 1 | 0 | 1 | +-----------------------------------+

To select the lines where status = 0 and the sample was grouped by user_id, also for each user_id counted:

  1. total points (total_point)
  2. number of points = 3 (point_3)
  3. number of points = 1 (point_1)

And they are ordered first by point_1, then point_3 and total_point.

  • And what you yourself tried to do. what kind of request have already reached - Mike
  • Something similar, but it does not work: SELECT user_id, SUM(point) AS total_point, COUNT(point) AS point_3 GROUP BY point HAVING point = 3, COUNT(point) AS point_1 GROUP BY point HAVING point = 1 FROM table` WHERE status = 0 GROUP BY user_id ORDER BY point_1, point_3, total_point` - Andrey

1 answer 1

You all mixed up in the request, group by is written once and after where, after it is written having and just one for the request. And having nothing will help you because it allows you only to remove some of the collected groups from the issue. I think you should ask a question on the similarity of "how to calculate the quantity by condition," the rest is less than you imagine.

 SELECT user_id, SUM(point) AS total_point, SUM(point=3) point_3, SUM(point=1) point_1 FROM table WHERE status = 0 GROUP BY user_id ORDER BY point_1,point_3,total_point

This syntax is for MySQL only; in other DBMSs, you would have to use sum(case point when 3 then 1 else 0 end) . In MySQL, the point=3 comparison operator returns 1 if the condition is met and 0 if not. sum simply summarizes the units received.

  • Thank you for the comprehensive answer, your answer helped me a lot! - Andrey