fork download
copy 
  1. CREATE TABLE `A` (
  2.   `id` int(11) NOT NULL,
  3.   `FlightId` int(11) DEFAULT NULL,
  4.   `Roles` varchar(30) DEFAULT NULL
  5. );
  6.  
  7. INSERT INTO `A` (`id`, `FlightId`, `Roles`) VALUES
  8. (1, 1, 'Pilot'),
  9. (2, 1, 'Steward'),
  10. (3, 1, 'Steward'),
  11. (4, 2, 'Pilot'),
  12. (5, 2, 'Co-Pilot');
  13.  
  14.  
  15.     SELECT a1.FlightId, COUNT(a2.FlightId) 
  16.     FROM A a1 LEFT JOIN A a2 ON a1.id = a2.id
  17.     AND a2.Roles = 'Steward'
  18.     GROUP BY a1.FlightId;
Success #stdin #stdout 0s 3440KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
1|2
2|0
https://ideone.com/BySBSx
language:
SQLite (sqlite 3.27.2)
created:
8 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!