postgresql group by

Question

This is my query in Postgresql:

SELECT 
    C.id, S.domen, B.name, C.source_id, ST.name 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6') 
ORDER BY "calltime" LIMIT 50

And I get this result:

Now I am trying to group by name(last column) this result to get result like this:

Контекстная реклама - 17
SEO-10
....

And for this I use this query:

SELECT 
    ST.name, count(ST.name) 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6') 
GROUP BY ST.name 
ORDER BY count(ST.name) DESC LIMIT 50

But I am getting wrong result:

Seems like it take values from source_id column. what am I doing wrong?

Try count(*) instead

Phil
– Phil

2018-02-22 03:58:42 +00:00
Commented Feb 22, 2018 at 3:58 — Phil
– Phil, Commented Feb 22, 2018 at 3:58
@Phil, The result is the same

Baurzhan
– Baurzhan

2018-02-22 04:02:10 +00:00
Commented Feb 22, 2018 at 4:02 — Baurzhan
– Baurzhan, Commented Feb 22, 2018 at 4:02

janith1024 · Accepted Answer · 2018-02-22 04:26:30Z

1

Try this, If you call group by before limit the result then group by for the entire records. So first filter then do the group by.

    SELECT 
        name, count(name) 
    FROM(
    SELECT 
        ST.name 
    FROM "calls" C 
        INNER JOIN "site" S ON S.id=C.site_id 
        INNER JOIN "sources" ON sources.id=C.source_id 
        INNER JOIN "brand" B ON B.id = S.id_brand
        INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
    WHERE 
        ("calltime" >= '2017-12-01') AND 
        ("calltime" <= '2017-12-03') AND 
        (S."id_brand"='6') 
    ORDER BY "calltime" LIMIT 50
    ) T 
    GROUP BY name

answered Feb 22, 2018 at 4:26

janith1024

1,0423 gold badges12 silver badges25 bronze badges

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

Comments

Laurenz Albe · Accepted Answer · 2018-02-22 04:23:08Z

0

The LIMIT 50 in the second query is executed after the GROUP BY.

If you want to aggregate only the first 50 lines, write the LIMIT 50 into a subselect and perform the GROUP BY in the outer SELECT.

answered Feb 22, 2018 at 4:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Comments

jwolf · Accepted Answer · 2018-02-22 04:36:58Z

It's possible that the 49 is just a coincidence. Try:

SELECT 
    ST.name, count(*) 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6') AND 
    C.source_id > 50
GROUP BY ST.name 
ORDER BY count(*) DESC LIMIT 50

All I did was change it to count(*) and add AND C.source_id > 50 to the Where clause. Let's see if this changes the count.

Also you could just run this:

SELECT 
    count(*) 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6')

This gives you a total count of all the rows (I removed the GROUP BY). If this number equals the total of your grouped row counts, then they really are the counts. We are looking for 127 which is 49 + 30 + 21 + 13 + 9 + 4 + 1

I hope this helps.

Collectives™ on Stack Overflow

postgresql group by

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related