1

I have the following table:

id   | name   | action | count
------------------------------
 1   | Value1 |    0   | 27
 1   | Value1 |    1   | 49
 2   | Value2 |    0   | 7
 2   | Value2 |    1   | 129
 3   | Value3 |    0   | 9
 3   | Value3 |    1   | 7

I need to make the 'action' column appear twice, with the count value of each line in it, something like this:

id   | name   | action1 | action2
---------------------------------
 1   | Value1 |    27   | 49
 2   | Value2 |    7    | 129
 3   | Value3 |    9    | 7

How can I do this? Here's my script:

SELECT m.id,
    t.name,
    m.action,
    count(m.id) AS count
FROM table1 m
LEFT JOIN table2 t ON (m.id = t.id)
WHERE m.status != '2'
GROUP BY m.id,
    t.name,
    m.action
ORDER BY 1, 3
  • 1
    Just a side note: Postgres 8.4 is end of life. You should really plan an upgrade to a supported version (e.g. 9.3). postgresql.org/support/versioning – a_horse_with_no_name Jul 15 '14 at 13:11
  • For now this is not possible at all. All of our data needs to be tested on newer versions and I'm not the guy responsible for that. But the upgrade is planned. – Renan Lazarotto Jul 15 '14 at 13:17
  • Your "table" does not seem to be a db table, but the result of your query. Definition (the relevant parts) of your actual underlying tables would allow us to give the best answer (\d tbl in psql). – Erwin Brandstetter Jul 15 '14 at 15:17
  • @a_horse_with_no_name already helped me with exactly what I needed. I'm sorry about the table thing, though, I'm used to see results as tables so I can extract data from them. – Renan Lazarotto Jul 21 '14 at 19:24
4

You can use a conditional aggregate:

select m.id, 
       t.name, 
       count(case when m.action = 0 then m.id end) as action1,
       count(case when m.action = 1 then m.id end) as action2
from table1 m
  left table2 t ON (m.id = t.id)
where m.status <> '2'
group by m.id, t.name;

In the upcoming 9.4 release the count(case(...)) could be written a bit shorter using the new "filtered" aggregate: count(id) filter (when m.action = 0)

  • I had to hide some sensitive information, that join is with another table where I get the name from. I think that because of this I still see the rows doubled. There is anything I can do about it? – Renan Lazarotto Jul 15 '14 at 13:15
  • @gh0st_h4wk: the group by m.id, t.name will not show "rows double" - at least not rows with the same combination of (id, name). If this is not what you want, you need to supply a better example (ideally the two tables populated with some sample data). My first version assumed that your table has a column count until I realized that your example "table" isn't your table but the result of your query – a_horse_with_no_name Jul 15 '14 at 13:17
  • I just had to hide one of the group by statements I had, now it is the way I wanted. Sorry about the mistake though! Thanks! – Renan Lazarotto Jul 15 '14 at 13:20
  • +1 on the new FILTER feature. Details in the devel manual. – Erwin Brandstetter Jul 15 '14 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.