CODEWITHSUNDEEP
postgresql
pewpewlasers
pewpewlasers

Reputation: 3215

Selecting other columns not in count, group by

So I have a table as follows

product_id      sender_id      timestamp      ...other columns...
1               2              1222
1               2              3423
1               2              1231
2               2              890
3               4              234
2               3              234234

I want to get rows where sender_id = 2, but I want to count and group by product_id and sort by timestamp descending. This means I need the following result

product_id      sender_id      timestamp      count      ...other columns...
1               2              3423           3
2               2              890            1

I tried the following query:

SELECT product_id, sender_id, timestamp, count(product_id), ...other columns...
FROM table
WHERE sender_id = 2
GROUP BY product_id

But I get the following error Error in query: ERROR: column "table.sender_id" must appear in the GROUP BY clause or be used in an aggregate function

Seems like I cannot SELECT columns that are not in the GROUP BY. Another method which I found online was to join

SELECT product_id, sender_id, timestamp, count, ...other columns...
FROM table
JOIN (
    SELECT product_id, COUNT(product_id) AS count
    FROM table
    GROUP BY (product_id)
) table1 ON table.product_id = table1.product_id
WHERE sender_id = 2
GROUP BY product_id

But doing this simply lists all rows without grouping or counting. My guess is that the ON part simply extends table again.

Upvotes: 0

Views: 228

Answers (2)

Chanukya
Chanukya

Reputation: 36

Just do a workout with your data:

CREATE TABLE products (product_id INTEGER,
sender_id INTEGER,
time_stamp INTEGER)

INSERT INTO products VALUES
(1,2,1222),
(1,2,3423),
(1,2,1231),
(2,2,890),
(3,4,234),
(2,3,234234)

SELECT  product_id,sender_id,string_agg(time_stamp::text,','),count(product_id) 
FROM products
WHERE sender_id=2
GROUP BY product_id,sender_id

Here you have distinct time_stamp ,so you need to apply some aggregate or just remove that column in select statement.

If you remove time_stamp in select statement then it would be very easy like below :

SELECT  product_id,sender_id,count(product_id) 
FROM products
WHERE sender_id=2
GROUP BY product_id,sender_id

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42773

Try grouping using product_id, sender_id 

select product_id, sender_id, count(product_id), max(timestamp) maxtm 
from t
where sender_id = 2
group by product_id, sender_id
order by maxtm desc

If you want other columns too:

select t.*, t1.product_count
from t
inner join (
    select product_id, sender_id, count(product_id) product_count, max(timestamp) maxtm 
    from t
    where sender_id = 2
    group by product_id, sender_id
) t1
on t.product_id = t1.product_id and t.sender_id = t1.sender_id and t.timestamp = t1.maxtm
order by t1.maxtm desc

Upvotes: 1

Related Questions