×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Show rows results on same row when Group BY fields

thread436-1816388

Show rows results on same row when Group BY fields

Show rows results on same row when Group BY fields

(OP)
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 --> MySQL

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.

RE: Show rows results on same row when Group BY fields

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,

glassesJust 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!

RE: Show rows results on same row when Group BY fields

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,

glassesJust 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!

RE: Show rows results on same row when Group BY fields

(OP)
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 --> MySQL

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



RE: Show rows results on same row when Group BY fields

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


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

Skip,

glassesJust 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!

RE: Show rows results on same row when Group BY fields

(OP)
That did the trick. Thank you for your help.

RE: Show rows results on same row when Group BY fields

Glad I could help.

Skip,

glassesJust 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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close