CODEWITHSUNDEEP
sqlmysqljoin
epitome
epitome

Reputation: 63

Self join to compare quarterly data from same table?

I'm helping a small rural health system add some databanking to their operations. They currently keep track of some operational statistics that they report each quarter.

I'd like to put the meat of these responses in one table and do historical comparisons from it. I'll have some additional tables for normalization, but here's the main one.

+-------+----------+-------------+----------+
| year  | quarter  | question_id | response |
+-------+----------+-------------+----------+
| 2000  | 1        | 100         | 50       |
+-------+----------+-------------+----------+
| 2000  | 2        | 100         | 100      |
+-------+----------+-------------+----------+
| 2000  | 1        | 200         | 75       |
+-------+----------+-------------+----------+
| 2000  | 2        | 200         | 25       |
+-------+----------+-------------+----------+

The output I'm looking for is to put those quarterly responses for each question side-by-side so I can calculate changes better and easy up my PHP processing. Below is the desired output:

+-------------+----------------------+----------------------+
| question_id | 1st quarter response | 2nd quarter response |
+-------------+----------------------+----------------------+
| 100         | 50                   | 100                  |
+-------------+----------------------+----------------------+
| 200         | 75                   | 25                   |
+-------------+----------------------+----------------------+

I'm new to SQL (using MySQL 5) and the only thing I can think of is I need a self join. I've tried but can't seem to get it. Am I approaching the table structure the correct way for this kind of historical analysis?

Any pointers would be a big help and a help to our project!

Thanks!

Upvotes: 1

Views: 1068

Answers (2)

Andomar
Andomar

Reputation: 238126

You could group by question, and then use a combination of case and max to select the answer for that quarter.

select  question_id
,       max(case when quarter = 1 then question_responses end) as Q1
,       max(case when quarter = 2 then question_responses end) as Q2
,       max(case when quarter = 3 then question_responses end) as Q3
,       max(case when quarter = 4 then question_responses end) as Q4
from    question_responses
where   year = 2000
group by
        question_id

In this example the max doesn't actually max anything, it ends up selecting the only quarter for which the case returns a value.

Upvotes: 1

reko_t
reko_t

Reputation: 56430

Well assuming you want to get that output for a specific year, eg. 2000:

SELECT
    question_id,
    (SELECT response FROM question_responses WHERE year = qr.year AND question_id = qr.question_id AND quarter = 1) AS qrt_resp_1,
    (SELECT response FROM question_responses WHERE year = qr.year AND question_id = qr.question_id AND quarter = 2) AS qrt_resp_2
    FROM question_responses AS qr
    WHERE year = 2000
    GROUP BY question_id

Upvotes: 0

Related Questions