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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Keith Medcalf >Sent: Tuesday, 22 October, 2019 08:11 >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