CODEWITHSUNDEEP
sqlsql-serverssms
gorrhyse
gorrhyse

Reputation: 1

SQL Server, aggregate CASE statements into one single row

I am in need to aggregate CASE statements in my select so they calculate into one row. Or i don't -have- to, but it would make a better spreadsheet afterwards

My query is something like this:

SELECT
    me.gardsnr
    ,me.bruksnr
    ,me.festenr
    ,me.seksjonsnr
    ,COUNT(Case when be.bruksenhetstypeKode = 'A' THEN (be.bruksenhetstypeKode)  END) AS 'A'
    ,COUNT(Case when be.bruksenhetstypeKode = 'B' THEN (be.bruksenhetstypeKode) END) AS 'B'
    ,COUNT(Case when be.bruksenhetstypeKode = 'F' THEN (be.bruksenhetstypeKode) END) AS 'F'
    ,COUNT(Case when be.bruksenhetstypeKode = 'I' THEN (be.bruksenhetstypeKode) END) AS 'I'
    ,COUNT(Case when be.bruksenhetstypeKode = 'U' THEN (be.bruksenhetstypeKode) END) AS 'U'
FROM 
    Bruksenhet be
INNER JOIN 
    Matrikkelenhet me ON be.matrikkelenhetID = me.id
GROUP BY
    me.gardsnr
    ,me.bruksnr
    ,me.festenr
    ,me.seksjonsnr
    ,be.bruksenhetstypeKode

This returns a result along the lines of:

example

Like row nr 1 and 2 - I would like to combine these two rows into one row i.e. just one row for gardsnr = 1, bruksnr = 4, festenr = 0 and seksjonsnr = 0 - with 5 in Column 'A' and 3 in column 'U'

Hope that made sense?

Upvotes: 0

Views: 913

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You don't need be.bruksenhetstypeKode in the GROUP BY. Remove it:

GROUP BY me.gardsnr, me.bruksnr, me.festenr, me.seksjonsnr

The structure of your COUNT()s is a bit complicated. I normally write this using SUM(), so I would just do:

SELECT me.gardsnr, me.bruksnr, me.festenr, me.seksjonsnr,
       SUM(Case when be.bruksenhetstypeKode = 'A' THEN 1 ELSE 0 END) AS A,
       SUM(Case when be.bruksenhetstypeKode = 'B' THEN 1 ELSE 0 END) AS B,
       SUM(Case when be.bruksenhetstypeKode = 'F' THEN 1 ELSE 0 END) AS F,
       SUM(Case when be.bruksenhetstypeKode = 'I' THEN 1 ELSE 0 END) AS I
FROM Bruksenhet be INNER JOIN
     Matrikkelenhet me
     ON be.matrikkelenhetID = me.id
GROUP BY me.gardsnr, me.bruksnr, me.festenr, me.seksjonsnr;

I would advise you NOT to use single quotes for column aliases. That just leads to confusion. Use single quotes only for string and date constants.

Upvotes: 1

Related Questions