CODEWITHSUNDEEP
mysqlsql
Cyber_Tron
Cyber_Tron

Reputation: 299

SQL Sum of String Occurrences with Group By

I have a table called battery_data, 

+-------+-------+-------+-------+-------+-------+-------+
|Module |Machine| Num1 |  Num3 | Field | Value | Value2 |
+-------+-------+-------+-------+-------+-------+-------+
| T-01  |  A&B  |   23.9|  123  |   Cell|  POS  | 328.34|
| T-01  |  A&B  |   27.0|  456  |Battery|  POS  |   23.8|
| T-01  |  C&D  | 409.01|  789  | EV    |  NEG  | NULL  |
| T-01  |  C&D  | 411.02|  124  | Cell  |  VSB  | NULL  |
| T-02  |  A&B  | 509.01|  989  | EV    |  NEG  | NULL  |
| T-02  |  A&B  | 611.02|  824  |Battery|  VSB  | NULL  |
+-------+-------+-------+-------+-------+-------+-------+

I want an output which returns the occurrences of all POS/NEG/VSB in Value field aggregated per unique module and unique machine. (There are only 2 types of machine: A&B, C&D)

If a certain Value(POS/NEG/VSB) isn't found for a module, 0 should be returned for that column.

I want to generate an output table as,

+-------+-------+-------+-------+-------+
|Module |Machine|  POS  |  NEG  |  VSB  |
+-------+-------+-------+-------+-------+
|  T-01 |  A&B  |   2   |  0    |   0   | 
|  T-01 |  C&D  |   0   |  1    |   1   | 
|  T-02 |  A&B  |   0   |  1    |   1   |   
+-------+-------+-------+-------+-------+

What should be the SQL query to achieve this?

Upvotes: 1

Views: 877

Answers (1)

Sebastian Paciuk
Sebastian Paciuk

Reputation: 149

Well, as you say, you want to find "the occurrences of all POS/NEG/VSB in Value field aggregated per unique module and unique machine". So first, you have to group by module and machine, and then for each row you have to count if it's value is POS or NEG or VSB. One way to do that is to have a different counter for every possible value, and then increase the counter that corresponds to the value of the current row. One way of doing that is the following:

SELECT Module, Machine,
SUM(CASE WHEN Value = 'POS' THEN 1 ELSE 0 END) AS POS,
SUM(CASE WHEN Value = 'NEG' THEN 1 ELSE 0 END) AS NEG,
SUM(CASE WHEN Value = 'VSB' THEN 1 ELSE 0 END) AS VSB
FROM battery_data
GROUP BY Module, Machine

Upvotes: 3

Related Questions