CODEWITHSUNDEEP
phpmysqlsql
GOUD
GOUD

Reputation: 53

How to summarize multiple fields in sql

How can I

V1,V2,V4,V5 already summarized data as v1,v2,v3,v4. How can I sum v1+v2 and v4+v5 and GTOT

+---------------------------------------------------------+
| Name | V1 | V2 | V1+V2 | V4 | V5 | V4+V5 |     GTOT     |
+---------------------------------------------------------+
|  abc |  2 | 5  |   7   |  0 |  1 |   1   |V1+V4 | v2+V5 |
|  xyz |  6 | 0  |   6   |  2 |  0 |   2   |V1+V4 | V2+V5 |
|  mnx |  1 | 2  |   3   |  5 |  3 |   8   |V1+V4 | V2+V5 |
|  bmx |  3 | 5  |   8   |  1 |  4 |   4   |V1+V4 | V2+V5 |
+---------------------------------------------------------+

I Used this code for summarized for v1,v2,v3,v4 as below

$result = $conn->query("select Name, sum(case when CAR = 'Red' and  VER='HIGH' then 1 else 0 end) AS 'V1',  
                     sum(case when CAR = 'Blue' and VER='HIGH' then 1 else 0 end) AS 'V2',
                     sum(case when CAR = 'Yellow' and  VER='HIGH' then 1 else 0 end) AS 'V4',
                     sum(case when CAR = 'White' and  VER='HIGH' then 1 else 0 end) AS 'V5'
FROM table1 group by Name");

How can I get the sum of v1+v2 and v4+v5 GTOT(v1+v4 and v2+v5) for these columns any idea about this.

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Probably the simplest method is to use a subquery or CTE:

select n.*,
       v1 + v2 as v1_2,
       v4 + v5 as v_f4_5
from (select Name,
             sum(CAR = 'Red' and VER = 'HIGH') AS V1,  
             sum(CAR = 'Blue' and VER = 'HIGH') AS V2,
             sum(CAR = 'Yellow' and VER = 'HIGH') AS V4,
             sum(CAR = 'White' and VER = 'HIGH') AS V5
      from table1
      group by Name
     ) n;

Notes:

  • MySQL has a handle method of counting the results of boolean expressions, using sum() with the boolean expression. In a numeric context, true is 1 and false is 0.
  • Only use single quotes for string and date constants; never use them for column names. That will just lead to hard-to-debug errors, because of the confusion between a string and column name.
  • You don't need the subquery; you can repeat the logic using, say: sum(car in ('Red', 'Blue') and ver = 'HIGH') as v1_2. Such logic can become harder to maintain.

Upvotes: 1

Related Questions