1

I used to have a query

SELECT ps_target_ecpm, ps_actual_ecpm
FROM publisher_stats
JOIN domain ON domain.dmn_id = ps_dmn_id
LEFT JOIN langue ON langue.lng_id = domain.default_lng_id
WHERE MONTH(ps_month) = 05 

The result I need should look like

may_target_ecmp, may_actual_ecpm, april_target_ecpm, april_actual_ecpm, march_target_ecpm, march_actual_ecpm.

For april MONTH(ps_month) = 04 and for march MONTH(ps_month) = 03 respectively.

After some questioning around I ended up with a query that looks like this

SELECT
(CASE WHEN MONTH(ps_month) = 4 THEN ps_target_ecpm ELSE 0 END) AS april_target_ecpm, 
(CASE WHEN MONTH(ps_month) = 4 THEN ps_actual_ecpm ELSE 0 END) AS april_actual_ecpm,
(CASE WHEN MONTH(ps_month) = 3 THEN ps_target_ecpm ELSE 0 END) AS march_target_ecpm, 
(CASE WHEN MONTH(ps_month) = 3 THEN ps_actual_ecpm ELSE 0 END) AS march_actual_ecpm 
FROM publisher_stats
JOIN domain ON domain.dmn_id = ps_dmn_id
LEFT JOIN langue ON langue.lng_id = domain.default_lng_id

The resultset I get is not quite what I need. The example response is:

0           0       0.48    0.27
0.48        0.47    0       0

While I need it to be in one row

0.48    0.47    0.48    0.27

Could you please help me to figure out how to make this query do what it is intended to. Thanks in advance

P.S. This question come all the way from this question - mysql pivoting - how can I fetch data from the same table into different columns?

Community
  • 1
  • 1
Vladimir Hraban
  • 3,543
  • 4
  • 26
  • 46

1 Answers1

3

Just use an aggregate function, MAX for example will work fine, but you might need to use SUM if you need to get the total for each month, if there are multiple entries for ps_target_ecpm for each month. Like this:

SELECT
  MAX(CASE WHEN MONTH(ps_month) = 4 THEN ps_target_ecpm ELSE 0 END) AS april_target_ecpm, 
  MAX(CASE WHEN MONTH(ps_month) = 4 THEN ps_actual_ecpm ELSE 0 END) AS april_actual_ecpm,
  MAX(CASE WHEN MONTH(ps_month) = 3 THEN ps_target_ecpm ELSE 0 END) AS march_target_ecpm, 
  MAX(CASE WHEN MONTH(ps_month) = 3 THEN ps_actual_ecpm ELSE 0 END) AS march_actual_ecpm 
FROM publisher_stats
JOIN domain ON domain.dmn_id = ps_dmn_id
LEFT JOIN langue ON langue.lng_id = domain.default_lng_id
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • @MarkBannister - Based on the sample data, both will give the exact results, but `SUM` if he wants the total if there are multiple entries for `ps_target_ecpm` for each month. I added this note, thanks. – Mahmoud Gamal May 21 '13 at 10:53
  • Guys, thank you very much, it works like a charm!. I just had to add GROUP BY dmn_id – Vladimir Hraban May 21 '13 at 11:09