Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show rows results on same row when Group BY fields 1

Status
Not open for further replies.
waubain

waubain

Technical User
Dec 13, 2011
200
US
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:

Code: 
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
TemperatureTable_f9ltv3.jpg


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.
 
Sort by date Sort by votes
Thy this...

Code: 
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
Or...
Code: 
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
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

Code: 
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

TemperatureTable_fvhpe3.jpg
 
Upvote 0 Downvote
Try this
Code: 
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
That did the trick. Thank you for your help.
 
Upvote 0 Downvote
Glad I could help.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Loomis
  • Locked
  • Question
stepping through table rows
Replies
7
Views
413
rashmibala
R
lupidol
  • Locked
  • Question
mysql cannot add rows with "UNION" 1
Replies
5
Views
695
Chris Miller
Chris Miller
Gerrit Broekhuis
  • Locked
  • Question
create connection by code
Replies
2
Views
648
Gerrit Broekhuis
Gerrit Broekhuis
boylesg
  • Locked
  • Question
Can't sort my table by a particular column, records are returned in autinc order.
Replies
4
Views
668
spamjim
spamjim
waubain
  • Locked
  • Question
mysqli_multi_query not not returning any results
Replies
2
Views
562
waubain
waubain

Part and Inventory Search

Sponsor

Back
Top