CODEWITHSUNDEEP
mysql
Alfy14
Alfy14

Reputation: 27

Is there a way to sum the same column depend of another one?

I have a table called "menergy" with the next structure and data example:

--------------------------------------------------------------
time | m_value | pt_periode | meter_id_meter | mtype_id_type |
--------------------------------------------------------------
t1   | 10      |  0         |      1         |      24       |
t1   |  5      |  0         |      1         |      25       |
t1   | 15      |  0         |      1         |      26       |
t1   | 20      |  0         |      1         |      27       |
t1   | 500     |  0         |      1         |      0002     |

There are 4 types of mtype so in case of mType = 24, 25, 26, 27 and not 0002, and the condition: same time, same pt_periode, same meter_id sum their m_value. So for example a condition could be: 

  (meter_id_meter = 1 and mtype_id_type = 24 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 25 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 26 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 27 and pt_periode = 0)

So with the last example of data the result I want is : 10 + 5 + 15 + 20 =50. So I want to get 50 with the operation I've read about IF condition and JOIN, but it needs to create another table. However, I've tryed the next but I had a syntax error:

SELECT
  timestampadd(month, -1, time_stamp) as "time",
  if ((meter_id_meter = 1 and mtype_id_type = 24 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 25 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 26 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 27 and pt_periode = 0), SUM(m_value)) as "Q"
FROM menergy

Upvotes: 2

Views: 28

Answers (2)

P.Salmon
P.Salmon

Reputation: 17615

I suspect you should be using a where clause.

SELECT   SUM(m_value)) as "Q"
FROM menergy
where meter_id_meter = 1 and pr_periode = 0 and mtype_id_type in (2,25,26,27)

but I have no idea what you are trying to do with timestampadd(month, -1, time_stamp) as "time",

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

Looking to your code seems you need sum the value where the if condition is true so try using where 

SELECT  timestampadd(month, -1, time_stamp) as "time", SUM(m_value)) as "Q"

FROM menergy
WHERE  (
  (meter_id_meter = 1 and mtype_id_type = 24 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 25 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 26 and pt_periode = 0) or
  (meter_id_meter = 1 and mtype_id_type = 27 and pt_periode = 0) 
)
group by  timestampadd(month, -1, time_stamp)

or simply 

WHERE meter_id  = 1  AND pt_periode = 0 and mtype in (24,25,26,27)

Upvotes: 1

Related Questions