David542 David542 - 1 month ago 15
MySQL Question

Aggregation comparison query

I have the following table of offers on iTunes, Google Play, and Microsoft:

`offers`
- id
- Title
- Year
- Platform
- Offer


An example entry might be something like:

(1, 'Titanic', 1997, 'iTunes', 'HDBUY')


This would mean that the film title Titanic (1997) is available for HDBUY on iTunes.

I have multiple platforms and I'd like to compare iTunes to other platforms to see which platform has more offers per title. Ideally, the result of my query would look like the following:

Title Year iTunes_Offers Top_Platform_Offers Top_Platform
Titanic 1997 HDBUY,SDBUY HDBUY,SDBUY,SDRENT Microsoft
Avatar 2009 HDBUY Google


Here is a schema with sample data to test with:

CREATE TABLE `offers` (
`id` varchar(20) DEFAULT NULL,
`Title` varchar(100) DEFAULT NULL,
`Year` varchar(20) DEFAULT NULL,
`Platform` varchar(100) DEFAULT NULL,
`Offer` varchar(20) DEFAULT NULL
);

INSERT INTO `offers` (`id`, `Title`, `Year`, `Platform`, `Offer`)
VALUES
('1', 'Titanic', '1997', 'iTunes', 'HDBUY'),
('1', 'Titanic', '1997', 'iTunes', 'SDBUY'),
('2', 'Titanic', '1997', 'Microsoft', 'SDBUY'),
('2', 'Titanic', '1997', 'Microsoft', 'HDBUY'),
('2', 'Titanic', '1997', 'Microsoft', 'SDRENT'),
('3', 'Titanic', '1997', 'Google', 'HDBUY'),
('4', 'Avatar', '2009', 'Google', 'HDBUY');


I can get all the offers per platform by doing the following query:

SELECT Title, Year, Platform, GROUP_CONCAT(Offer) GROUP BY id


Which gives me:

id Title Year Platform Offer group_concat(offer)
1 Titanic 1997 iTunes HDBUY HDBUY,SDBUY
2 Titanic 1997 Microsoft SDBUY SDBUY,HDBUY,SDRENT
3 Titanic 1997 Google HDBUY HDBUY
4 Avatar 2009 Google HDBUY HDBUY


But I'm not quite sure how to aggregate it so I can get the three columns together,
iTunes_Offers
,
Top_Platform_Offers
, and
Top_Platform
. To group between platforms I would like to aggregate by CONCAT(Title, Year) -- for example, "Psycho (2005)" is a different movie than "Psycho (1960)".

How would I accomplish this?

Answer

You can use conditional aggregation:

SELECT Title, Year, Platform,
       GROUP_CONCAT(CASE WHEN platform = 'iTunes'    THEN Offer END) AS iTunes,
       GROUP_CONCAT(CASE WHEN platform = 'Microsoft' THEN Offer END) AS Microsoft,
       GROUP_CONCAT(CASE WHEN platform = 'Google'    THEN Offer END) AS Google
FROM offers
GROUP BY CONCAT(title,year);

If you could have duplicates, then use GROUP_CONCAT(DISTINCT . . .).

Comments