CODEWITHSUNDEEP
sqlpostgresqlgroup-byaggregate-functions
AKIWEB
AKIWEB

Reputation: 19612

How to group by two columns to show only one single row?

Below is my query which I am running against Postgres database. 

SELECT ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
    ,mcd.col5
    ,AVG(mcd.col5 / md.col3) AS dc
    ,AVG(md.col3 / ad.col4) AS cb
FROM tableCount AS md
INNER JOIN tablePop AS ad ON ad.col1 = md.col1
    AND ad.col2 = md.col2
INNER JOIN tableData AS mcd ON mcd.col1 = md.col1
    AND mcd.col2 = md.col2
WHERE md.col2 = 23
    AND md.col1 = '1'
GROUP BY ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
    ,mcd.col5
ORDER BY md.col3 DESC limit 30
GROUP BY ad.col1
    ,ad.col2;

And below is the output I am getting back on the console with the use of above query- 

col1    col2    col3        col4        col5        dc                                  cb
1       23      48108       224123      479         0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      89          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      142         0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      1649        0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      14          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      203         0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      52          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      62          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      65          0.00000000000000000000              0.00000000000000000000
1       23      48108       224123      33          0.00000000000000000000              0.00000000000000000000

So the output I would like to see it is -

col1    col2    col3        col4        col5        dc                                  cb
1       23      48108       2241230     278         0.00000000000000000000              0.00000000000000000000

Is this possible to do in SQL?

P.S Any Fiddle example would be great.

Upvotes: 1

Views: 112

Answers (4)

Dan Bracuk
Dan Bracuk

Reputation: 20804

You can turn your query to a derived table by making it a subquery with an alias. Then you can select from it.

select col1, col2, col3, col4, col5, dc, sum(col3 + col4 + col5 + dc + cb) thesum
from (query from your question goes here) temp
group by col1, col2, col3, col4, col5, dc

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

You can SUM() or take the MAX(), really it's only col5 in your sample that is resulting in multiple rows, GROUP BY any non-aggregate field, and pick the appropriate aggregate for any other field:

SELECT ad.col1
    ,ad.col2
    ,MAX(md.col3)
    ,MAX(ad.col4)
    ,AVG(mcd.col5)
    ,AVG(mcd.col5 / md.col3) AS dc
    ,AVG(md.col3 / ad.col4) AS cb
FROM tableCount AS md
INNER JOIN tablePop AS ad ON ad.col1 = md.col1
    AND ad.col2 = md.col2
INNER JOIN tableData AS mcd ON mcd.col1 = md.col1
    AND mcd.col2 = md.col2
WHERE md.col2 = 23
    AND md.col1 = '1'
GROUP BY ad.col1
    ,ad.col2
ORDER BY md.col3 DESC limit 30

Upvotes: 1

Lajos Veres
Lajos Veres

Reputation: 13725

Maybe like this:

SELECT ad.col1
    ,ad.col2
    ,min(md.col3)
    ,min(ad.col4)
    ,min(mcd.col5)
    ,AVG(mcd.col5 / md.col3) AS dc
    ,AVG(md.col3 / ad.col4) AS cb
FROM tableCount AS md
INNER JOIN tablePop AS ad ON ad.col1 = md.col1
    AND ad.col2 = md.col2
INNER JOIN tableData AS mcd ON mcd.col1 = md.col1
    AND mcd.col2 = md.col2
WHERE md.col2 = 23
    AND md.col1 = '1'
GROUP BY ad.col1
    ,ad.col2
ORDER BY min(md.col3)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You want to remove col5 from the group by:

SELECT ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
    ,sum(mcd.col5) as col5
    ,AVG(mcd.col5 / md.col3) AS dc
    ,AVG(md.col3 / ad.col4) AS cb
FROM tableCount AS md
INNER JOIN tablePop AS ad ON ad.col1 = md.col1
    AND ad.col2 = md.col2
INNER JOIN tableData AS mcd ON mcd.col1 = md.col1
    AND mcd.col2 = md.col2
WHERE md.col2 = 23
    AND md.col1 = '1'
GROUP BY ad.col1
    ,ad.col2
    ,md.col3
    ,ad.col4
ORDER BY md.col3 DESC limit 30

I assume the last group by and order by are there accidentally.

Upvotes: 1

Related Questions