Show rows results on same row when Group BY fields 1

[waubain]

I am collecting soil temperatures at different depths. Different crops have different temperature depths.
My data structure looks like this for each record for each location and depth. The data is the same for each day for this example.

ObsDate:
Location: (field location)
Depth: (for this crop I am measuring at 4", 12" 24" 36")
Temperature:

SELECT 
	ObsDate,
	Location,
	CASE WHEN Depth = 4 THEN Temperature END Temp4,
    CASE WHEN Depth = 12 THEN Temperature END Temp12,
    CASE WHEN Depth = 24 THEN Temperature END Temp24,
    CASE WHEN Depth = 36 THEN Temperature END Temp36
FROM `temperature`
GROUP BY ObsDate, Location

table picture showing only 1 location

What I am trying to do is have one row of data for each date and location with a column for each depth. When I added the GROUP BY line I only get Temp4 column and not the others.

Thank you for any help.

 

[SkipVought]

Thy this...

Select a.ObsDate, a.Location, 
  Sum(a.Temp4) Temp_4, 
  Sum(a.Temp12) Temp_12, 
  Sum(a.Temp24) Temp_24, 
  Sum(a.Temp36) Temp_36
From 

  (SELECT 
	ObsDate,
	Location,
	CASE WHEN Depth = 4 THEN Temperature END Temp4,
        CASE WHEN Depth = 12 THEN Temperature END Temp12,
        CASE WHEN Depth = 24 THEN Temperature END Temp24,
        CASE WHEN Depth = 36 THEN Temperature END Temp36
  FROM `temperature`
  GROUP BY ObsDate, Location ) a
Group By a.ObsDate, a.Location

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Or...

SELECT 
	ObsDate,
	Location,
	SUM(CASE WHEN Depth = 4  THEN Temperature ELSE 0 END) Temp4,
        SUM(CASE WHEN Depth = 12 THEN Temperature ELSE 0 END) Temp12,
        SUM(CASE WHEN Depth = 24 THEN Temperature ELSE 0 END) Temp24,
        SUM(CASE WHEN Depth = 36 THEN Temperature ELSE 0 END) Temp36
FROM `temperature`
GROUP BY ObsDate, Location

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[waubain]

Skip, Thanks for the reply. I only get results for ObsDate, Location, and Temp_4. The other Temp_ columns are NULL. I am using PHPadmin to test this so not sure if the problem is on my side or something else.

Here is the MySQL I am using to test and the resulting table

DROP TEMPORARY TABLE IF EXISTS tblTemperature;
CREATE temporary table tblTemperature
(
ID int auto_increment Primary key,
ObsDate date,
Location varchar(15),
Depth int(2),
Temperature int(3)
);

insert into tblTemperature(ObsDate, Location, Depth, Temperature)
values
('2022-05-13','Field N',4,60),
('2022-05-13','Field N',12,57),
('2022-05-13','Field N',24,53),
('2022-05-13','Field N',36,51),
('2022-05-13','Garden',4,65),
('2022-05-13','Garden',12,62),
('2022-05-13','Garden',24,58),
('2022-05-13','Garden',36,55),
('2022-05-14','Field N',4,61),
('2022-05-14','Field N',12,58),
('2022-05-14','Field N',24,54),
('2022-05-14','Field N',36,50),
('2022-05-14','Garden',4,66),
('2022-05-14','Garden',12,63),
('2022-05-14','Garden',24,54),
('2022-05-14','Garden',36,56)
;

Select a.ObsDate, a.Location, 
  Sum(a.Temp4) Temp_4, 
  Sum(a.Temp12) Temp_12, 
  Sum(a.Temp24) Temp_24, 
  Sum(a.Temp36) Temp_36
From 

  (SELECT 
	ObsDate,
	Location,
	CASE WHEN Depth = 4 THEN Temperature END Temp4,
        CASE WHEN Depth = 12 THEN Temperature END Temp12,
        CASE WHEN Depth = 24 THEN Temperature END Temp24,
        CASE WHEN Depth = 36 THEN Temperature END Temp36
  FROM `tblTemperature`
  GROUP BY ObsDate, Location ) a
Group By a.ObsDate, a.Location

 

[SkipVought]

Try this

SELECT 
	ObsDate,
	Location,
	SUM(CASE WHEN Depth = 4  THEN Temperature ELSE 0 END) Temp4,
        SUM(CASE WHEN Depth = 12 THEN Temperature ELSE 0 END) Temp12,
        SUM(CASE WHEN Depth = 24 THEN Temperature ELSE 0 END) Temp24,
        SUM(CASE WHEN Depth = 36 THEN Temperature ELSE 0 END) Temp36
FROM `temperature`
GROUP BY ObsDate, Location

My results

[pre]
ObsDate Location 'Temp4' 'Temp12' 'Temp24' 'Temp36'
5/13/2022 Field N 60 57 53 51
5/13/2022 Garden 65 62 58 55
5/14/2022 Field N 61 58 54 50
5/14/2022 Garden 66 63 54 56

[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[waubain]

That did the trick. Thank you for your help.

 

[SkipVought]

Glad I could help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!