Avatar of MartinC
MartinC asked on

SQL for getting two values per result from one table

Hi all ... scratching my head over this, but I suspect experts here may know it ...

If a table has fields:

PersonID (integer)
GMTDate (date)
TimePeriod (integer)
Channel (integer)
ReadIngValue  (real)


Lets say the data looks like this:

PersonID     GMTDate     TimePeriod    Channel   ReadingValue
1            15-05-2008      1            7         4.6
1            15-05-2008      1            8         2.6
1            15-05-2008      1            9         3.1
1            15-05-2008      2            7         2.9
1            15-05-2008      2            8         4.0
1            15-05-2008      2            9         4.1
1            16-05-2008      1            7         3.3
1            16-05-2008      1            8         3.7
1            16-05-2008      1            9         0.4
2            15-05-2008      1            7         2.2
 

... and I need to get results back into one line of an SQL query from where I identify a date range and PersonID, and want TWO ReadingValue results for twoChannel number = 7 and Channel Number = 9 ... so that the result looks like this:

PersonID     GMTDate     TimePeriod   Channel7ReadingValue    Channel9ReadingValue
1            15-05-2008      1               4.6                      3.1
1            15-05-2008      2               2.9                      4.1
1            16-05-2008      1               3.3                      0.4

How should my SQL look?
SQL

Avatar of undefined
Last Comment
MartinC

8/22/2022 - Mon
Nerdwood

Hi MartinC

Try something like this:
SELECT DISTINCT     D.PersonID, D.GMTDate, D.TimePeriod, (
  SELECT ReadingValue
  FROM tblData 7
  WHERE 7.PersonID = D.PersonID
    AND 7.GMTDate = D.GMTDate
    AND 7.TimePeriod = D.TimePeriod
    AND 7.Channel = 7
) AS Channel7ReadingValue, (
  SELECT ReadingValue
  FROM tblData 9
  WHERE 9.PersonID = D.PersonID
    AND 9.GMTDate = D.GMTDate
    AND 9.TimePeriod = D.TimePeriod
    AND 9.Channel = 9
) AS Channel9ReadingValue
FROM         tblData D

Open in new window

Nerdwood

Sorry, here's the version that works! Just had to re-create your table...
SELECT DISTINCT D.PersonID, D.GMTDate, D.TimePeriod, (
  SELECT ReadingValue
  FROM tblData tblData7
  WHERE tblData7.PersonID = D.PersonID
    AND tblData7.GMTDate = D.GMTDate
    AND tblData7.TimePeriod = D.TimePeriod
    AND tblData7.Channel = 7
) AS Channel7ReadingValue, (
  SELECT ReadingValue
  FROM tblData tblData9
  WHERE tblData9.PersonID = D.PersonID
    AND tblData9.GMTDate = D.GMTDate
    AND tblData9.TimePeriod = D.TimePeriod
    AND tblData9.Channel = 9
) AS Channel9ReadingValue
FROM         tblData D

Open in new window

Thomasian

hi MartinC,

Try
SELECT PersonID, GMTDate, TimePeriod
      ,SUM(CASE WHEN Channel=7 THEN ReadingValue END) Channel7ReadingValue
      ,SUM(CASE WHEN Channel=9 THEN ReadingValue END) Channel9ReadingValue
FROM tablename
WHERE Channel IN (7,9)
GROUP BY PersonID, GMTDate, TimePeriod

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
rstomar

use PIVOT
rstomar

with PIVOT column values can be shown as columns
ASKER CERTIFIED SOLUTION
koutny

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
MartinC

Rats, my initial question left out a crucial grouping function ... so I have to confess I couldn't actually run  these exact approaches. However koutny's response formed the basis for what I eventually used so I'll give the points there. Thanks everybody for your suggestions.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.