seo Question posted in
Some basic information to get you started with this can be found here.

postgresql group by – SEO

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:

enter image description here

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:

enter image description here

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

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search