Postgres group by

Question

When I run a query, these are the results presented to me:

id  account_id  score  active  item_id
5    78          9      true    4
6    78          1      true    4
7    78          9      true    6
8    78          5      true    7
9    78          5      true    8
10   78          5      true    8

I'd like the output to look like this by combining item_id's based on score:

id  account_id  score  active  item_id
*    78          10      true    4
7    78          9      true    6
8    78          5      true    7
*    78          10      true    8

My query that returns that info looks like this:

SELECT item.id, item.account_id, itemaudit.score, itemrevision.active, itemaudit.item_id
from item 
left join itemrevision on item.id = itemrevision.id 
join itemaudit on item.id = itemaudit.id 
where itemrevision.active = true 
;

The bit I'm missing is when 'item_id' is not distinct, combine/sum the value of 'score'. I'm not sure how to do this step.

The schema looks like this:

 CREATE TABLE item
(id integer, account_id integer);

CREATE TABLE itemaudit
(id integer, item_id integer, score integer);

CREATE TABLE itemrevision
(id int, active boolean, item_id int);


INSERT INTO item 
  (id, account_id)
VALUES
    (5, 78),
    (6, 78),
    (7, 78),
    (8, 78),
    (9, 78),
    (10, 78)    
;


INSERT INTO itemaudit
    (id, item_id, score)
VALUES
    (5, 4, 5),
    (6, 4, 1),
    (7, 6, 9),
    (8, 7, 10),
    (9, 8, 1),
    (10, 8, 9)  
;

INSERT INTO itemrevision
    (id, active, item_id)
VALUES
    (5, true, 4),
    (6, true, 4),
    (7, true, 6),
    (8, true, 7),
    (9, true, 7),
    (10, true, 8)
;

Why did you revert my edit? I added the CREATE TABLE AS for you... — Evan Carroll
– Evan Carroll, Commented Apr 14, 2017 at 21:33
I reverted the edit because it gave the impression that data was in an existing table while in fact its the results of a query across several different ables. I wanted to avoid that confusion. — Publiccert
– Publiccert, Commented Apr 14, 2017 at 21:36
That's the output I get from my initial query. Its not desirable. I want the output of my query to look like the output at the bottom of my question. I'll edit to demonstrate this better. — Publiccert
– Publiccert, Commented Apr 14, 2017 at 21:42
muchh better. ( I would delete the grid and bad query and just leave that and what you want.) — Evan Carroll
– Evan Carroll, Commented Apr 14, 2017 at 21:44

Gordon Linoff · Accepted Answer · 2017-04-14 00:57:10Z

1

If I understand correctly, you just want an aggregation query:

select ia.item_id, sum(ia.score) as score
from item i join  -- the `where` clause turns this into an inner join
     itemrevision ir
     on i.id = ir.id  join
     itemaudit ia
     on i.id = ia.id 
where ir.active = true 
group by ia.item_id;

Notes:

I changed the left join to an inner join, because the where clause has this effect anyway.
Table aliases make the query easier to write and to read.
In an aggregation query, the other columns are not appropriate.

answered Apr 14, 2017 at 0:57

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Publiccert Over a year ago

That solved the initial problem, thank you! Is there a way to retain the structure I showed in my example but combine 'item_id' rows and add their score into that row?

Publiccert Over a year ago

I've added an example of the desired output.

Evan Carroll · Accepted Answer · 2017-04-14 21:29:52Z

I think you want something like this..

SELECT
  CASE
    WHEN array_length(array_agg(id),1) = 1
      THEN (array_agg(id))[1]::text
    ELSE '*'
  END AS id,
  account_id,
  sum(score) AS score,
  item_id
FROM item
GROUP BY account_id, item_id
ORDER BY account_id, item_id;

 id | account_id | score | item_id 
----+------------+-------+---------
 *  |         78 |    10 |       4
 7  |         78 |     9 |       6
 8  |         78 |     5 |       7
 *  |         78 |    10 |       8
(4 rows)

While this is what you want the simpler versions is more detailed and better.

SELECT
  array_agg(id) AS id,
  account_id,
  sum(score) AS score,
  item_id
FROM item
GROUP BY account_id, item_id
ORDER BY account_id, item_id;

   id   | account_id | score | item_id 
--------+------------+-------+---------
 {5,6}  |         78 |    10 |       4
 {7}    |         78 |     9 |       6
 {8}    |         78 |     5 |       7
 {9,10} |         78 |    10 |       8
(4 rows)

Collectives™ on Stack Overflow

Postgres group by

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related