Or something like this using the new filter clause from 3.30. As written it
should show it with two decimal points.
(not tested for typos)
select
CITY_TXT as CITY,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'WALKING') /
total(Employee_Count), 2) as WALKING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CYCLING') /
total(Employee_Count), 2) as CYCLING,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'PUBLIC TRANSIT')
/ total(Employee_Count), 2) as PUBLIC_TRANSIT,
round(100.0 * total(Employee_Count) filter (where MODE_TXT = 'CAR') /
total(Employee_Count), 2) as CAR,
round(100.0 * total(Employee_Count) filter (where MODE_TXT not in ('WALKING',
'CYCLING', 'PUBLIC TRANSIT', 'CAR')) / total(Employee_Count), 2) as OTHER,
from
(
select
CITY_TXT,
MODE_TXT,
count(*) as Employee_Count
from
Employees
inner join Cities
using (CITY_ID)
inner join Mode
using (MODE_ID)
group by CITY_TXT, MODE_TXT
)
group by CITY_TXT
order by CITY;
-----Original Message-----
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of
Keith Medcalf
Sent: Tuesday, October 22, 2019 10:11 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Can SQLite do this in a single query?
CREATE TABLE employees(employee_id, city_id, mode_id);
CREATE TABLE citys(city_id, city_txt);
CREATE TABLE modes(mode_id, mode_txt);
select city_txt,
mode_txt,
total(1) over (partition by city_txt, mode_txt) / total(1) over
(partition by city_txt) as percentage
from employees, citys, modes
where employees.city_id == citys.city_id
and employees.mode_id == modes.mode_id
group by city_txt, mode_txt
group by city_txt, mode_txt;
You have to paint the output table yourself.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Winfried
>Sent: Tuesday, 22 October, 2019 07:23
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Yes, I forgot the column mode_id in the Employee's table.
>
>Thanks, I'll read up on the features SQLite's SELECT has to offer. At
>worst,
>I'll just run a simpler query multiple times.
>
>https://www.sqlite.org/lang_select.html
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
