Re: [sqlite] Can SQLite do this in a single query?

[David Raymond]

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