Merge Rows By Group

Question

I have weather data from weather stations in all the states.

Here is a basic query I've been running.

-- Weather pattern for continental US    
SELECT 
    YEAR(Date), 
    MONTH(Date),
    DataType,
    AVG(Value)
FROM 
    Station_Data 
WHERE
    Stations_ID NOT IN (
        SELECT 
            Stations.ID 
        FROM 
            Stations 
        JOIN States
            States 
        ON 
            States.ID = Stations.States_ID 
        WHERE 
            States.Name = "Alaska" OR States.Name = "Hawaii"
    ) AND
    YEAR(Date) = 2000
GROUP BY 
    YEAR(Date), 
    MONTH(Date),
    DataType;

and the output is the following

I want to convert the output to have separate columns for MMNT, MMXT, MNTM. I tried using solutions provided in Efficiently convert rows to columns in sql server and My Sql merging rows

-- Weather pattern for continental US    
SELECT 
    YEAR(Date), 
    MONTH(Date),
    AVG(CASE WHEN (DataType = "MMNT") THEN Value ELSE 0 END) AS MMNT,
    AVG(CASE WHEN (DataType = "MMXT") THEN Value ELSE 0 END) AS MMXT,
    AVG(CASE WHEN (DataType = "MNTM") THEN Value ELSE 0 END) AS MNTM
FROM 
    Station_Data 
WHERE
    Stations_ID NOT IN (
        SELECT 
            Stations.ID 
        FROM 
            Stations 
        JOIN States
            States 
        ON 
            States.ID = Stations.States_ID 
        WHERE 
            States.Name = "Alaska" OR States.Name = "Hawaii"
    ) AND
    YEAR(Date) = 2000
GROUP BY 
    YEAR(Date), 
    MONTH(Date),
    DataType;

but my result looks like this...

what's wrong with the statement?

-- UPDATE

I tried removing DataType from GROUP and I get this result...

But I think the value is wrong.

SELECT 
    AVG(Value)
FROM 
    Station_Data 
WHERE
    Stations_ID NOT IN (
        SELECT 
            Stations.ID 
        FROM 
            Stations 
        JOIN States
            States 
        ON 
            States.ID = Stations.States_ID 
        WHERE 
            States.Name = "Alaska" OR States.Name = "Hawaii"
    ) 
    AND YEAR(Date) = 2000
    AND MONTH(Date) = 1
    AND DataType = "MMNT";

score 1 · Accepted Answer · answered May 05 '16 at 21:03

1

You don't need to group by DataType. This is the answer. Otherwise your aggregate function works within single value of the type, so you are always geting one column that is not zero.

answered May 05 '16 at 21:03

Giorgi Nakeuri

35,155
8
47
75

I tried that, but it seems like the value changes. I updated the post with your suggestion. – YarGnawh May 05 '16 at 21:17
1

Update your conditions to ` THEN Value ELSE NULL END`. 0's gets counted in average but NULLs will be ignored. – Gustek May 05 '16 at 21:20
1

In aggregate don't do `else 0` – Giorgi Nakeuri May 05 '16 at 21:20

Merge Rows By Group

1 Answers1