This eliminates the duplicate denominator calculation:
select city_txt,
mode_txt,
(
select total(1)
from employees, citys, modes
where employees.city_id == citys.city_id
and employees.mode_id == modes.mode_id
and citys.city_id == C.city_id
and modes.mode_id == M.mode_id
) / employee_count as percentage
from (
select city_txt,
citys.city_id,
total(1) as employee_count
from employees, citys
where employees.city_id == citys.city_id
group by city_txt
order by city_txt
) as C, modes as M
order by city_txt, mode_txt;
and of course you can wrap it in a classifier if you like:
select city_txt,
sum(case when mode_txt = 'WALKING' then percentage end) as Walking,
sum(case when mode_txt = 'CYCLING' then percentage end) as Cycling,
sum(case when mode_txt = 'PUBLIC TRANSIT' then percentage end) as
"Public Transit",
sum(case when mode_txt = 'CAR' then percentage end) as Car,
sum(case when mode_txt not in ('WALKING', 'CYCLING', 'PUBLIC TRANSIT',
'CAR') then percentage end) as Other
from (
select city_txt,
mode_txt,
(
select total(1)
from employees, citys, modes
where employees.city_id == citys.city_id
and employees.mode_id == modes.mode_id
and citys.city_id == C.city_id
and modes.mode_id == M.mode_id
) / employee_count as percentage
from (
select city_txt,
citys.city_id,
total(1) as employee_count
from employees, citys
where employees.city_id == citys.city_id
group by city_txt
order by city_txt
) as C, modes as M
)
group by city_txt
order by city_txt;
This is "standard SQL" and should work with just about any RDBMS using SQL of
any variation.
--
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 <[email protected]> On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 22 October, 2019 09:44
>To: SQLite mailing list <[email protected]>
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>
>Hmmm. That gives the wrong answers compared to doing it the "old
>fashioned way". You still have to "paint the fence" yourself, though now
>the table data is not sparse, thus easier to paint (since each value goes
>in the next cell, and you start a new row when the city_txt changes).
>You calculate the denominator excessively, but if really necessary you
>can optimize that.
>
> select city_txt,
> mode_txt,
> (select total(1)
> from employees, citys, modes
> where employees.city_id == citys.city_id
> and employees.mode_id == modes.mode_id
> and citys.city_id == C.city_id
> and modes.mode_id == M.mode_id) / (select total(1)
> from employees, citys
> where employees.city_id
>== citys.city_id
> and citys.city_id ==
>C.City_id) as percentage
> from citys C, modes M
>order by city_txt, mode_txt;
>
>
>and you get the column headings thusly (the left most column is of course
>your static text "City") ...
>
> select mode_txt
> from modes
>order by mode_txt
>
>or if you do not like to put static column headings in your fence
>painter, then:
>
>select 'City'
>union all
>select mode_txt
> from (select mode_txt
> from modes
> order by mode_txt);
>
>Of course, perhaps you are using arbitrary "data hiding" headings and not
>the actual data (a method often used by PHB's to prove an pre-ordained
>outcome) in which case other methods may be more appropriate to "hide"
>what you do not want to see and provide it in a format compatible with
>that pre-ordained output).
>
>--
>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 <[email protected]> On
>>Behalf Of Keith Medcalf
>>Sent: Tuesday, 22 October, 2019 08:11
>>To: SQLite mailing list <[email protected]>
>>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 <[email protected]> On
>>>Behalf Of Winfried
>>>Sent: Tuesday, 22 October, 2019 07:23
>>>To: [email protected]
>>>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
>>>[email protected]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[email protected]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
