CODEWITHSUNDEEP
mysqlsql
user12649790
user12649790

Reputation:

Sum Two Generated Columns

Say I have a table like 

+------------------+-----+
| ID TIPE A TIPE B  |    |
+------------------+-----+
| 1    52    4      |    |
| 2    41    7      |    |
| 3    30    4      |    |
+------------------+-----+

where the TIPE A and TIPE B are generated columns. I'd like to create a column TIPE C that will be the sum of TIPE A and TIPE B.

+--------------------------+----+
| ID TIPE A TIPE B  TIPE C |    |
+--------------------------+-----+
| 1    52    4       56    |    |
| 2    41    7       48    |    |
| 3    30    4       34    |    |
+--------------------------+----+

My SELECT is something like 

SELECT
c.id AS 'ID',
SUM(CASE
    WHEN code = 0 THEN 1
    ELSE 0
END) 'TIPE A',
SUM(CASE
    WHEN mt.tipe LIKE '%tipe_b%' THEN 1
    ELSE 0
END) AS 'TIPE B'
FROM 
[...]

Is it possible to do this?

Upvotes: 1

Views: 217

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

In MySQL, you can significantly simplify the expressions. But you do need to repeat them:

SELECT c.id AS ID, SUM(code = 0) as tipe_a, SUM(mt.tipe LIKE '%tipe_b%') as tipe_b,
        SUM( (code = 0) OR mt.tipe LIKE '%tipe_b%')) as tipe_a_or_b,
        SUM(code = 0) + SUM(mt.tipe LIKE '%tipe_b%') as sum_either
FROM t;

You seem to specify sum_either, but I think you might want tipe_a_or_b.

Upvotes: 0

czdepski
czdepski

Reputation: 326

I can see two ways for you to do this:

By using subqueries

select 'ID', 
       'TIPE A', 
       'TIPE B', 
       'TIPE A' + 'TIPE B' as 'TIPE C'
  from ([your actual select statement])

or create a new column with the combined logic for 'TIPE A' and 'TIPE B'

...
SUM(CASE
    WHEN code = 0 THEN 1
    ELSE 0
END + 
CASE
    WHEN mt.tipe LIKE '%tipe_b%' THEN 1
    ELSE 0
END) 'TIPE C'
...

Depending on the complexity of your query one way or another can fit best.

Upvotes: 1

GRVPrasad
GRVPrasad

Reputation: 1142

You can do simple way

SELECT *, (TIPE A + TIPE B) as TIPE C FROM table;

Upvotes: 2

Related Questions