CODEWITHSUNDEEP
mysqlsql
Rob Banmeadows
Rob Banmeadows

Reputation: 105

Having difficulty in constructing SQL query for events

I have a problem where I'm trying to get counts of data associated with multiple events occurring on separate days.

Let's say I'm following a group of plane-spotters, each of whom may spot many planes from around the world on any one day, while based at some particular airport. I'd like to produce a list consisting of one row per spotter per day, with columns for the spotter's ID, the date, how many planes he (it's always "he", right?) spotted on that day, how many individual airlines the planes belonged to, and how many countries the airlines belonged to. So, I'd like to have results like this:

 +-----------+------------+---------+----------+-----------+
 |           |            |  Planes |          |   Airline |
 | SpotterID | Date       | spotted | Airlines | Countries |
 +-----------+------------+---------+----------+-----------+
 |      1234 | 2017-04-15 |      28 |       11 |         4 |
 +-----------+------------+---------+----------+-----------+
 |      1234 | 2017-04-16 |      65 |       19 |         7 |
 +-----------+------------+---------+----------+-----------+
 |      5678 | 2017-04-22 |      39 |       14 |         6 |
 +-----------+------------+---------+----------+-----------+
 |      6677 | 2017-04-28 |      74 |       29 |         9 |
 +-----------+------------+---------+----------+-----------+

So, (MySQL 5.7.17) my test tables are as sketched out below (I may have forgotten a couple of indexes).

The plane-spotting events table is defined as:

CREATE TABLE `SpottingEvents` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `SpotterID` int(11) NOT NULL,
 `SpotDateTime` datetime NOT NULL,
 `PlaneID` int(11) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_SpotterID_PlaneID_DateTime` (`SpotterID`,`PlaneID`,`SpotDateTime`),
 KEY `indx_SpotterID_DateTime` (`SpotterID`,`SpotDateTime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

the planes table is defined as:

CREATE TABLE `Planes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `PlaneID` int(11) NOT NULL,
 `PlaneTypeID` int(11) NOT NULL,
 `AirlineID` int(11) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_PlaneID_AirlineID` (`PlaneID`,`AirlineID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

the airlines table is defined as:

CREATE TABLE `Airlines` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `AirlineID` int(11) NOT NULL,
 `AirlineName` varchar(100) NOT NULL,
 `CountryID` int(11) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_AirlineID` (`AirlineID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and the countries table is defined as:

CREATE TABLE `Countries` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `CountryID` int(11) NOT NULL,
 `CountryName` varchar(100) NOT NULL,
 `Notes` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `indx_CountryID` (`CountryID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

My problem is with the last two columns, the Airlines and Countries counts. I've tried several ways to do this, including the following:

select distinct sev.SpotDateTime, sev.SpotterID, count(*) as planes_count,
(select count(*) from ( select distinct cnt.CountryID 
  from Countries as cnt
  inner join Airlines as aln on al.CountryID = cnt.CountryID
  inner join Planes as pl on pl.AirlineID = aln.AirlineID 
  where pl.PlaneID = sev.PlaneID ) as t1) as countries_count  
from SpottingEvents as sev
# where sev.UserID = 1234
group by SpotDateTime
order by SpotDateTime

which leads to an error Unknown column 'sev.planeID' in 'where clause' but since I'm no expert, I'm just not getting the intended results. So, how do I achieve the desired results?

Upvotes: 1

Views: 56

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You are looking for COUNT(DISTINCT). Join the tables needed, then count.

select
  se.spotterid,
  date(se.spotdatetime) as spot_date,
  count(*) as planes,
  count(distinct p.airlineid) as airlines,
  count(distinct a.countryid) as countries
from spottingevents se
join planes p on p.planeid = se.planeid
join airlines a on a.airlineid = p.airlineid
group by se.spotterid, date(se.spotdatetime)
order by date(se.spotdatetime), se.spotterid;

Upvotes: 1

Related Questions