×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

# Contact US

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

 Forum Search FAQs Links MVPs

## 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,

Just traded in my OLD subtlety...
for a NUance!

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

Just traded in my OLD subtlety...
for a NUance!

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

Just traded in my OLD subtlety...
for a NUance!

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

Just traded in my OLD subtlety...
for a NUance!

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

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.