Home mysql How to calculate the number of strings by means of grouping

How to calculate the number of strings by means of grouping

Author

Date

Category

Good day, please tell me at what a question:

There is a table of type:

+ ----------------------- +.
| 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 SQL request to get a table of type:

+ ----------------------- ------------ +.
| User_ID | Total_Point | Point_3 | Point_1 |
-------------------------------------
| 1 | 6 | 2 | 0 |
-------------------------------------
| 3 | 4 | 1 | 1 |
-------------------------------------
| 2 | 1 | 0 | 1 |
+ ----------------------------------- +

To select the strings 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. Points = 1 (point_1)

and are ordered first by point_1, then Point_3 and Total_point.


Answer 1, Authority 100%

You are confusedly confused in the query, Group BY is written once and after where it is written by Having and just alone on request. And having you will not help anything because it allows you to remove some collected groups from issuing. I think you would have to ask a question on the similarity “How to Calculate the amount by condition”, the rest then you have more than 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

The syntax is exclusively for MySQL, in other DBMS it would have to use SUn (Case Point When 3 Then 1 ELSE 0 END) . In MySQL, the comparison operator point = 3 returns 1 if the condition is executed and 0 if not. Sum simply summarizes the received units.

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions