fork(1) download
copy 
  1. CREATE TABLE DivvyTrips(
  2.   trip_id INT PRIMARY KEY,	
  3.   starttime	TIMESTAMP,
  4.   stoptime	TIMESTAMP,
  5.   bikeid	INT,
  6.   tripduration	INT,
  7.   from_station_id INT,	
  8.   from_station_name	VARCHAR(100),
  9.   to_station_id	INT,
  10.   to_station_name VARCHAR(100),
  11.   usertype VARCHAR(30),
  12.   gender VARCHAR(20),
  13.   birthyear INT);
  14.   INSERT INTO DivvyTrips VALUES
  15. (12979227,'12/31/2016 23:53:18','1/1/2017 00:08:13', 5114, 895, 195,'Columbus Dr & Randolph St', 25, 'Michigan Ave & Pearson St', 'Customer', '', NULL),
  16. (12979226,'12/31/2016 23:53:07','1/1/2017 00:08:38', 1026, 931, 195,'Columbus Dr & Randolph St', 25, 'Michigan Ave & Pearson St', 'Customer', '', NULL),
  17. (12979225,'12/31/2016 23:51:31','1/1/2017 00:07:41', 504, 970, 199,'Wabash Ave & Grand Ave', 35, 'Streeter Dr & Grand Ave', 'Subscriber', 'Male', 1985),
  18. (12979224,'12/31/2016 23:51:31','1/1/2017 00:07:51', 4451, 980, 199,'Wabash Ave & Grand Ave', 35, 'Streeter Dr & Grand Ave', 'Subscriber', 'Female', 1985),
  19. (12979223,'12/31/2016 23:47:40','12/31/2016 23:50:39', 5643, 179, 47,'State St & Kinzie St', 125, 'Rush St & Hubbard St', 'Subscriber', 'Male', 1970),
  20. (12979222,'12/31/2016 23:47:33','1/1/2017 00:18:36', 48, 1863, 177,'Theater on the Lake', 140, 'Dearborn Pkwy & Delaware Pl', 'Customer', '', NULL),
  21. (12979221,'12/31/2016 23:47:03','1/1/2017 00:18:10', 2865, 1867, 177,'Theater on the Lake', 140, 'Dearborn Pkwy & Delaware Pl', 'Customer', '', NULL),
  22. (12979220,'12/31/2016 23:45:41','1/1/2017 00:13:17', 1779, 1656, 195,'Columbus Dr & Randolph St', 195, 'Columbus Dr & Randolph St', 'Customer', '', NULL),
  23. (12979219,'12/31/2016 23:44:54','12/31/2016 23:46:42', 518, 108, 264,'Stetson Ave & South Water St', 52, 'Michigan Ave & Lake St', 'Subscriber', 'Male', 1986),
  24. (12979218,'12/31/2016 23:43:41','12/31/2016 23:53:18', 658, 577, 15,'Racine Ave & 18th St', 42, 'Wabash Ave & Cermak Rd', 'Subscriber', 'Male', 1991),
  25. (12979217,'12/31/2016 23:43:27','1/1/2017 00:42:04', 5253, 3517, 77,'Clinton St & Madison St', 77, 'Clinton St & Madison St', 'Customer', '', NULL),
  26. (12979216,'12/31/2016 23:43:27','12/31/2016 23:54:37', 3237, 670, 427,'Cottage Grove Ave & 63rd St', 418, 'Ellis Ave & 53rd St', 'Subscriber', 'Male', 1991),
  27. (12979215,'12/31/2016 23:43:25','12/31/2016 23:53:59', 4615, 634, 405,'Wentworth Ave & 35th St', 367, 'Racine Ave & 35th St', 'Subscriber', 'Male', 1986),
  28. (12979214,'12/31/2016 23:43:11','1/1/2017 00:42:04', 2748, 3533, 77,'Clinton St & Madison St', 77, 'Clinton St & Madison St', 'Customer', '', NULL),
  29. (12979213,'12/31/2016 23:37:47','12/31/2016 23:49:31', 4664, 704, 256,'Broadway & Sheridan Rd', 349, 'Halsted St & Wrightwood Ave', 'Subscriber', 'Male', 1965),
  30. (12979212,'12/31/2016 23:36:20','12/31/2016 23:45:53', 794, 573, 256,'Broadway & Sheridan Rd', 226, 'Racine Ave & Belmont Ave', 'Subscriber', 'Male', 1990),
  31. (12979211,'12/31/2016 23:35:42','12/31/2016 23:47:16', 5643, 694, 175,'Wells St & Polk St', 47, 'State St & Kinzie St', 'Subscriber', 'Male', 1970),
  32. (12979210,'12/31/2016 23:35:19','12/31/2016 23:50:55', 2783, 936, 239,'Western Ave & Leland Ave', 455, 'Maplewood Ave & Peterson Ave', 'Subscriber', 'Male', 1994),
  33. (12979209,'12/31/2016 23:32:05','12/31/2016 23:48:00', 1092, 955, 130,'Damen Ave & Division St', 502, 'California Ave & Altgeld St', 'Customer', '', NULL),
  34. (12979208,'12/31/2016 23:28:18','12/31/2016 23:31:04', 4255, 166, 267,'Lake Park Ave & 47th St', 413, 'Woodlawn Ave & Lake Park Ave', 'Subscriber', 'Male', 1965);
  35.  
  36.  
  37. Select from_station_name or to_station_name AS station_name, Max(num_trips)
  38. From (Select from_station_name, count(*) AS num_trips
  39. 	  From DivvyTrips
  40. 	  Where usertype="Subscriber" AND gender="Female"
  41. 	  Group by from_station_name), (Select to_station_name, count(*) AS num_trips
  42. 									From DivvyTrips
  43. 									Where usertype="Subscriber" AND gender="Female"
  44. 									Group by to_station_name)
  45. Group by station_name
  46.  
  47.  
  48.  
  49.  
Success #stdin #stdout #stderr 0s 18088KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 37: ambiguous column name: num_trips
https://ideone.com/3LB0sF
language:
SQLite (sqlite 3.27.2)
created:
7 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!