YarGnawh YarGnawh - 1 year ago 53
MySQL Question

Merge Rows By Group

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

db model

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

Result 1

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...

Result 2

what's wrong with the statement?

-- UPDATE

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

Result 4

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";


Result 5

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download