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
