Join Multiple Sql statements from the same table

Question

I have read many post on this topic but I am not yet satisfied.

I have a table called ticket with the following columns

TicketID | AirlineID | PassengerID | TicketPrice | TicketVolume | DestinationCountry | ExitCountry | TicketDate`

I have multiple queries like

SELECT AVG(TicketPrice) 
FROM ticket 
WHERE TicketPrice between 552 and 1302
AND AirlineID=1 
AND TicketDate between '2016-01-01' and '2016-12-31' 
GROUP BY TicketDate

SELECT AVG(TicketPrice) 
FROM ticket 
WHERE TicketPrice between 552 and 1302 
AND AirlineID=1 
AND TicketDate between '2017-01-01' and '2017-12-31' 
GROUP BY TicketDate

Please how can I join both queries to form another table side by side

+--------------------------------++-----------
| AirlineID || Average Ticket Price 2016/2017|
+--------------------------------++-----------

they are actually more queries.

This makes no sense so far. You select AirlineID=1. And in your results you have a column AirlineID. Of course it will only contein ones. Then you group by TicketDate, so as to get one row per ticket date. But your result doesn>'t contain a column TicketDate, so you'll get many rows, not knowing what date refer to. Please clarify. — Thorsten Kettner, Mar 21 '18 at 10:38
"they are actually more queries". Aha. What are these queries like? Same, but different year? What is the result to look like then? One column per year? Do you know beforehand which years or shall these depend on the current date or the years existing in the table? — Thorsten Kettner, Mar 21 '18 at 10:42

score 5 · Accepted Answer · edited Mar 21 '18 at 11:03

5

Simply use CASE to achieve this:

Try this:

SELECT
    AirlineID,
    AVG(CASE WHEN TicketDate BETWEEN '2016-01-01' AND '2016-12-31' THEN TicketPrice END),
    AVG(CASE WHEN TicketDate BETWEEN '2017-01-01' AND '2017-12-31' THEN TicketPrice END) 
FROM ticket 
WHERE
    TicketPrice BETWEEN 552 AND 1302 AND
    AirlineID = 1 
GROUP BY
    AirlineID, TicketDate;

edited Mar 21 '18 at 11:03

Tim Biegeleisen

502,043
27
286
360

answered Mar 21 '18 at 10:16

DineshDB

5,998
7
33
49

Thanks @DineshDB but I also was to return the AirlineID as a column because I have up to 6 AirlineIDs. When I tried your query, it returned only the case columns – Destiny Alabi Mar 21 '18 at 10:25
I tried it and added the between AirlineIDs to obtain my result. – Destiny Alabi Mar 21 '18 at 10:37

Joakim Danielson · Answer 2 · 2018-03-21T10:49:10.117

2

Here is my solution for this, maybe not the desired output by OP but in my opinion a better output and also no need for hardcoded dates

SELECT AirlineID, extract(YEAR from TicketDate) as year, AVG(TicketPrice)
FROM ticket
WHERE TicketPrice between 552 and 1302
GROUP BY AirLineID, year

edited Mar 21 '18 at 10:49

answered Mar 21 '18 at 10:36

Joakim Danielson

43,251
5
22
52

Complained of the group clause – Destiny Alabi Mar 21 '18 at 10:39
Not for me on MariaDb, what database did you try on? Maybe change year to something else if it is a problem with that word, like avg_year. – Joakim Danielson Mar 21 '18 at 10:41
Per year you are selecting the avarage ticket price and an arbitrarily picked airline ID. Depending on settings MySQL may allow this or not. You may have to use `ANY_VALUE(AirlineID)` to achieve that. But then: Is this really your intention or have you made a mistake in your query? Maybe you want AirlineID in the `GROUP BY` clause instead. – Thorsten Kettner Mar 21 '18 at 10:45
@ThorstenKettner, yes I had forgotten airlineid in my group by. Thank you. – Joakim Danielson Mar 21 '18 at 10:48
@ThorstenKettner, is it [this setting discussed here](https://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) you are referring to? – Joakim Danielson Mar 21 '18 at 10:55
Yes, AirlineID is not functionally dependent on year and must hence be either in the `GROUP BY` clause or get aggregated (via `ANY_VALUE`). The `ONLY_FULL_GROUP_BY` mode ensures that you obey this. – Thorsten Kettner Mar 21 '18 at 10:57
@DestinyAlabi, group by should work now with my latest update – Joakim Danielson Mar 21 '18 at 11:05

score 1 · Answer 3 · answered Mar 21 '18 at 10:16

1

If you want to merge the results into a single table, use UNION.

SELECT {...}
UNION
SELECT {...}

The SELECT statements must return the same columns

answered Mar 21 '18 at 10:16

baroale

56
1
3

1

This would give the two averages as separate _rows_, not columns. – Tim Biegeleisen Mar 21 '18 at 10:19
WhenI tried this, it put everything tor the same column – Destiny Alabi Mar 21 '18 at 10:21

SanjaySinghRawat · Answer 4 · 2018-03-22T05:13:49.780

0

SQL

 SELECT 
 (SELECT AVG(TicketPrice) FROM ticket 
    WHERE TicketPrice between 552 and 1302
    AND AirlineID=1 
    AND TicketDate between '2016-01-01' and '2016-12-31' 
    GROUP BY TicketDate) as Col1,
(SELECT AVG(TicketPrice) FROM ticket 
    WHERE TicketPrice between 552 and 1302 
    AND AirlineID=1 
    AND TicketDate between '2017-01-01' and '2017-12-31' 
    GROUP BY TicketDate) as Col2

edited Mar 22 '18 at 05:13

answered Mar 21 '18 at 10:25

SanjaySinghRawat

57
4

ERROR 1242 (21000): Subquery returns more than 1 row – Destiny Alabi Mar 21 '18 at 10:35

Join Multiple Sql statements from the same table

4 Answers4