Can SQLite do this in a single query?

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Can SQLite do this in a single query?

Winfried
Hello,

Before I dive into https://www.sqlitetutorial.net, I'd like to check with
experienced users whether SQLite is up to the task, or I should maybe run
multiple queries possibly with some help from PHP.

Using the following tables, I need to find how employees from each city come
to work.
====== Employees table:
EMPLOYEE_ID | CITY_ID
Cities table:
CITY_ID | CITY_TXT
Mode table:
MODE_ID | MODE_TXT

This is the type of output I need to get ultimately:
CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
City1 | 15% | 5% | 50% [ 25% | 5%

Do you think it can it be done in a single query using a mixture of COUNT(),
SUM(), GROUP BY, HAVING etc., or will I have to split the task into a few
independent queries?

Thank you.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Keith Medcalf

On Monday, 21 October, 2019 08:31, Winfried <[hidden email]> wrote:

>Using the following tables, I need to find how employees from each city
>come to work.

>====== Employees table:
>EMPLOYEE_ID | CITY_ID
>Cities table:
>CITY_ID | CITY_TXT
>Mode table:
>MODE_ID | MODE_TXT
>
>This is the type of output I need to get ultimately:
>CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
>City1 | 15% | 5% | 50% [ 25% | 5%

No amount of queries or magical incantations will get you the results you are asking for because there is no way to get from MODE_TXT to CITY_TXT.  That is, there is no way to compose the rest of the query as indicated by the ... to obtain the data required to solve the problem:

select EMPLOYEE_ID, CITY_TXT, MODE_TXT
  from EMPLOYEES, CITIES, MODE
 where ....

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Bart Smissaert
Shouldn't there be field MODE_ID in the Employee table?

RBS

On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf <[hidden email]> wrote:

>
> On Monday, 21 October, 2019 08:31, Winfried <[hidden email]> wrote:
>
> >Using the following tables, I need to find how employees from each city
> >come to work.
>
> >====== Employees table:
> >EMPLOYEE_ID | CITY_ID
> >Cities table:
> >CITY_ID | CITY_TXT
> >Mode table:
> >MODE_ID | MODE_TXT
> >
> >This is the type of output I need to get ultimately:
> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
> >City1 | 15% | 5% | 50% [ 25% | 5%
>
> No amount of queries or magical incantations will get you the results you
> are asking for because there is no way to get from MODE_TXT to CITY_TXT.
> That is, there is no way to compose the rest of the query as indicated by
> the ... to obtain the data required to solve the problem:
>
> select EMPLOYEE_ID, CITY_TXT, MODE_TXT
>   from EMPLOYEES, CITIES, MODE
>  where ....
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Petr Jakeš
> Shouldn't there be field MODE_ID in the Employee table?
>

It looks like a high school homework to me :D
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Keith Medcalf
In reply to this post by Bart Smissaert

On Monday, 21 October, 2019 08:47, Bart Smissaert <[hidden email]> wrote:

>Shouldn't there be field MODE_ID in the Employee table?

Not a clue.  I didn't write the schema.  Perhaps there is another table called MODATES that has a link to the employee (EMPLOYEE_ID) and a link to the mode (MODE_ID) together with the start and end dates to which that linkage applies.  And yet another table that called CIDATES that has a link the employee (EMPLOYEE_ID) and to the city (CITY_ID) together with the start and end dates to which that linkage applies.  (In which case there is still no solution since the problem is inadequately stated.

Also, what about the people that walk except when it is raining and then they take the bus unless in either case they wake up late and drive themselves in their own car.  Except of course on Tuesday when the neighbour is going to the supermarket at the same time as they happen to be going to work so they hitch a ride with the neighbour in the neighbours car so they can smoke a phatty on the way, unless it is the second Tuesday of the month in which case they ride their bike because it is "Patch Tuesday" and, you know, just to be safe.  Except of course if there was an earthquake in which case they just stay home.

The point being that there is no way to solve the problem stated using the given information, and no way to correct for the missing information since one has no clue what it is or how complicated it is.

>On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf <[hidden email]> wrote:

>> On Monday, 21 October, 2019 08:31, Winfried <[hidden email]> wrote:
>>
>> >Using the following tables, I need to find how employees from each
>> city come to work.
>>
>> >====== Employees table:
>> >EMPLOYEE_ID | CITY_ID
>> >Cities table:
>> >CITY_ID | CITY_TXT
>> >Mode table:
>> >MODE_ID | MODE_TXT
>> >
>> >This is the type of output I need to get ultimately:
>> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
>> >City1 | 15% | 5% | 50% [ 25% | 5%
>>
>> No amount of queries or magical incantations will get you the results
>you
>> are asking for because there is no way to get from MODE_TXT to
>CITY_TXT.
>> That is, there is no way to compose the rest of the query as indicated
>by
>> the ... to obtain the data required to solve the problem:
>>
>> select EMPLOYEE_ID, CITY_TXT, MODE_TXT
>>   from EMPLOYEES, CITIES, MODE
>>  where ....
>>
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Bart Smissaert
> Not a clue.  I didn't write the schema.

I know, I asked the OP.

RBS

On Mon, Oct 21, 2019 at 4:16 PM Keith Medcalf <[hidden email]> wrote:

>
> On Monday, 21 October, 2019 08:47, Bart Smissaert <
> [hidden email]> wrote:
>
> >Shouldn't there be field MODE_ID in the Employee table?
>
> Not a clue.  I didn't write the schema.  Perhaps there is another table
> called MODATES that has a link to the employee (EMPLOYEE_ID) and a link to
> the mode (MODE_ID) together with the start and end dates to which that
> linkage applies.  And yet another table that called CIDATES that has a link
> the employee (EMPLOYEE_ID) and to the city (CITY_ID) together with the
> start and end dates to which that linkage applies.  (In which case there is
> still no solution since the problem is inadequately stated.
>
> Also, what about the people that walk except when it is raining and then
> they take the bus unless in either case they wake up late and drive
> themselves in their own car.  Except of course on Tuesday when the
> neighbour is going to the supermarket at the same time as they happen to be
> going to work so they hitch a ride with the neighbour in the neighbours car
> so they can smoke a phatty on the way, unless it is the second Tuesday of
> the month in which case they ride their bike because it is "Patch Tuesday"
> and, you know, just to be safe.  Except of course if there was an
> earthquake in which case they just stay home.
>
> The point being that there is no way to solve the problem stated using the
> given information, and no way to correct for the missing information since
> one has no clue what it is or how complicated it is.
>
> >On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf <[hidden email]>
> wrote:
>
> >> On Monday, 21 October, 2019 08:31, Winfried <[hidden email]>
> wrote:
> >>
> >> >Using the following tables, I need to find how employees from each
> >> city come to work.
> >>
> >> >====== Employees table:
> >> >EMPLOYEE_ID | CITY_ID
> >> >Cities table:
> >> >CITY_ID | CITY_TXT
> >> >Mode table:
> >> >MODE_ID | MODE_TXT
> >> >
> >> >This is the type of output I need to get ultimately:
> >> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
> >> >City1 | 15% | 5% | 50% [ 25% | 5%
> >>
> >> No amount of queries or magical incantations will get you the results
> >you
> >> are asking for because there is no way to get from MODE_TXT to
> >CITY_TXT.
> >> That is, there is no way to compose the rest of the query as indicated
> >by
> >> the ... to obtain the data required to solve the problem:
> >>
> >> select EMPLOYEE_ID, CITY_TXT, MODE_TXT
> >>   from EMPLOYEES, CITIES, MODE
> >>  where ....
> >>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Winfried
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Petr Jakeš
> 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.
>

Will the results of simple query multiple times help you somehow?

Think about how to join two or more tables together (not difficult to find
info about SQL JOIN)

My reply is to help you to try you think how SQL basically works.

>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Keith Medcalf
In reply to this post by Winfried
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 <[hidden email]> On
>Behalf Of Winfried
>Sent: Tuesday, 22 October, 2019 07:23
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> On Behalf Of Keith Medcalf
Sent: Tuesday, October 22, 2019 10:11 AM
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> On
>Behalf Of Winfried
>Sent: Tuesday, 22 October, 2019 07:23
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Keith Medcalf
In reply to this post by Keith Medcalf

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 <[hidden email]> On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 22 October, 2019 08:11
>To: SQLite mailing list <[hidden email]>
>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 <[hidden email]> On
>>Behalf Of Winfried
>>Sent: Tuesday, 22 October, 2019 07:23
>>To: [hidden email]
>>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
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Keith Medcalf

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 <[hidden email]> On
>Behalf Of Keith Medcalf
>Sent: Tuesday, 22 October, 2019 09:44
>To: SQLite mailing list <[hidden email]>
>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 <[hidden email]> On
>>Behalf Of Keith Medcalf
>>Sent: Tuesday, 22 October, 2019 08:11
>>To: SQLite mailing list <[hidden email]>
>>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 <[hidden email]> On
>>>Behalf Of Winfried
>>>Sent: Tuesday, 22 October, 2019 07:23
>>>To: [hidden email]
>>>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
>>>[hidden email]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Doug
In reply to this post by Keith Medcalf
Please explain one phrase in the select:
  total(1) over (partition by city_txt, mode_txt)
Is "total" a function and does "1" refer to city_txt?
I can't wrap my head around what's going on with this phrase.
Doug

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Keith Medcalf
> Sent: Tuesday, October 22, 2019 7:11 AM
> To: SQLite mailing list <[hidden email]>
> 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 <[hidden email]>
> On
> >Behalf Of Winfried
> >Sent: Tuesday, 22 October, 2019 07:23
> >To: [hidden email]
> >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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Keith Medcalf

On Tuesday, 22 October, 2019 23:24, Doug <[hidden email]> wrote:

>Please explain one phrase in the select:
>  total(1) over (partition by city_txt, mode_txt)
>Is "total" a function and does "1" refer to city_txt?
>I can't wrap my head around what's going on with this phrase.

total() is a built-in aggregate like sum() -- there are just some minor (but important) differences.

sum() returns NULL if there was nothing to sum.  Also, if the values were all integers and the result fits in an integer, an integer is returned.

total() returns 0.0 if is has nothing to total, and the returned value is always floating-point.

So total(1) means the floating-point value of adding up all the 1 values ... that it, it is count(*) in floating-point, or basically cast(count(*) as float) but somewhat shorter.

This is because the integer expression 37/154 has the integer result 0, however the floating point expression 37.0 / 154.0 has result 0.24025974025974

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Winfried
Thank you very much to all. There's no way I could have come up with those
queries.

Considering the wealth/complexity of SELECT even in SQLite, it seems a whole
book could be written on just that command.

https://www.sqlite.org/images/syntax/select-stmt.gif

Is there a good book you would recommend to learn about SQLite, especially
regarding SELECT?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Simon Slavin-3
On 24 Oct 2019, at 10:18am, Winfried <[hidden email]> wrote:

> Is there a good book you would recommend to learn about SQLite, especially regarding SELECT?

A list already made:

<https://sqlite.org/books.html>

Books long and short, concentrating on different things.

You might also be interested in books about SQL, the query language used by SQLite and many other database engines.  This would include various complicated things that can be done using SELECT.  Unfortunately I don't learn well from books, so I can't recommend any.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Winfried
Simon Slavin-3 wrote
> https://sqlite.org/books.html

Thanks.

"You have to paint the output table yourself." "You still have to "paint the
fence" yourself, though now the table data is not sparse, thus easier to
paint"

Looks like "paint" is DB lingo: Does it mean formatting the output after
running the right SELECT ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Keith Medcalf

Well, "paint" is to draw your output.  So, for example, you can output an arbitrary matrix like this:

select columnHeader from theColumns order by columnHeader;

from which you can output "<table><tr><th></th>"
then for each row retrieved output "<th>" columnHeader "</th>"
and when you run out of rows
"</tr>"
and keep track of how many rows you read.

Then select your data and paint it

select data from ....

and if you are currently on column 0 output "<tr>"
then output "<td>" data "</td>"
and if you just output the last column reset your internal column counter to 0 and output "</tr>"
and when you run out of stuff to output, then write "</table>"

voila -- you just "painted an html table"


--
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 <[hidden email]> On
>Behalf Of Winfried
>Sent: Thursday, 24 October, 2019 17:11
>To: [hidden email]
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Simon Slavin-3 wrote
>> https://sqlite.org/books.html
>
>Thanks.
>
>"You have to paint the output table yourself." "You still have to "paint
>the
>fence" yourself, though now the table data is not sparse, thus easier to
>paint"
>
>Looks like "paint" is DB lingo: Does it mean formatting the output after
>running the right SELECT ?
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite do this in a single query?

Winfried
Keith Medcalf wrote
> Well, "paint" is to draw your output.

Thank you.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users