Get data in one query

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

Get data in one query

Cecil Westerhof-5
I have a table messages in which something is put every minute. The total
messages that are added today I can get with:
    SELECT COUNT(*) AS Total
    FROM   messages
    WHERE  date = DATE('now')

And the number of messages that where entered today, but not at the start
of a minute I can get with:
    SELECT COUNT(*) AS Late
    FROM   messages
    WHERE  date = DATE('now')
       AND time NOT LIKE '%:00'

Is there a way to get this information in one query?

--
Cecil Westerhof
_______________________________________________
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: Get data in one query

Simon Slavin-3
On 29 Aug 2018, at 4:56pm, Cecil Westerhof <[hidden email]> wrote:

> Is there a way to get this information in one query?

You can use this structure:

SELECT (first SELECT), (second SELECT)

Simon.
_______________________________________________
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: Get data in one query

Simon Slavin-3
On 29 Aug 2018, at 4:59pm, Simon Slavin <[hidden email]> wrote:

> You can use this structure:
>
> SELECT (first SELECT), (second SELECT)

Better still in your case, you can use

    SELECT (first SELECT) as Total, (second SELECT) as Late

Simon.
_______________________________________________
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: Get data in one query

R Smith-2
In reply to this post by Cecil Westerhof-5

SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
           FROM messages
          WHERE date = DATE('now')
        )



On 2018/08/29 5:56 PM, Cecil Westerhof wrote:

> I have a table messages in which something is put every minute. The total
> messages that are added today I can get with:
>      SELECT COUNT(*) AS Total
>      FROM   messages
>      WHERE  date = DATE('now')
>
> And the number of messages that where entered today, but not at the start
> of a minute I can get with:
>      SELECT COUNT(*) AS Late
>      FROM   messages
>      WHERE  date = DATE('now')
>         AND time NOT LIKE '%:00'
>
> Is there a way to get this information in one query?
>

_______________________________________________
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: Get data in one query

Cecil Westerhof-5
2018-08-29 18:06 GMT+02:00 R Smith <[hidden email]>:

>
> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>           FROM messages
>          WHERE date = DATE('now')
>        )
>

Works like a charm. Thanks.

I made it even more useful:
SELECT Total
,       Late
,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
FROM    (
    SELECT SUM(total) AS Total
    ,      SUM(late)  AS Late
    FROM  (
        SELECT 1                      AS Total
        ,      (time NOT LIKE '%:00') AS Late
        FROM   messages
        WHERE  date = DATE('now')
    )
)



> On 2018/08/29 5:56 PM, Cecil Westerhof wrote:
>
>> I have a table messages in which something is put every minute. The total
>> messages that are added today I can get with:
>>      SELECT COUNT(*) AS Total
>>      FROM   messages
>>      WHERE  date = DATE('now')
>>
>> And the number of messages that where entered today, but not at the start
>> of a minute I can get with:
>>      SELECT COUNT(*) AS Late
>>      FROM   messages
>>      WHERE  date = DATE('now')
>>         AND time NOT LIKE '%:00'
>>
>> Is there a way to get this information in one query?
>>
>
--
Cecil Westerhof
_______________________________________________
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: Get data in one query

Cecil Westerhof-5
2018-08-29 18:58 GMT+02:00 Cecil Westerhof <[hidden email]>:

> 2018-08-29 18:06 GMT+02:00 R Smith <[hidden email]>:
>
>>
>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>>           FROM messages
>>          WHERE date = DATE('now')
>>        )
>>
>
> Works like a charm. Thanks.
>
> I made it even more useful:
> SELECT Total
> ,       Late
> ,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> FROM    (
>     SELECT SUM(total) AS Total
>     ,      SUM(late)  AS Late
>     FROM  (
>         SELECT 1                      AS Total
>         ,      (time NOT LIKE '%:00') AS Late
>         FROM   messages
>         WHERE  date = DATE('now')
>     )
> )
>

And even more useful:
SELECT date
,       Total
,       Late
,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
FROM    (
    SELECT date
    ,      SUM(total) AS Total
    ,      SUM(late)  AS Late
    FROM  (
        SELECT date
        ,       1                      AS Total
        ,      (time NOT LIKE '%:00') AS Late
        FROM   messages
        WHERE  date >= DATE('now', '-7 days')
           AND date  < DATE('now')
    )
    GROUP BY date
)
ORDER BY date DESC

--
Cecil Westerhof
_______________________________________________
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: Get data in one query

wmertens
By the way, why not store the time as epoch? Date and time in one...

On Wed, Aug 29, 2018, 8:46 PM Cecil Westerhof <[hidden email]>
wrote:

> 2018-08-29 18:58 GMT+02:00 Cecil Westerhof <[hidden email]>:
>
> > 2018-08-29 18:06 GMT+02:00 R Smith <[hidden email]>:
> >
> >>
> >> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
> >>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
> >>           FROM messages
> >>          WHERE date = DATE('now')
> >>        )
> >>
> >
> > Works like a charm. Thanks.
> >
> > I made it even more useful:
> > SELECT Total
> > ,       Late
> > ,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> > FROM    (
> >     SELECT SUM(total) AS Total
> >     ,      SUM(late)  AS Late
> >     FROM  (
> >         SELECT 1                      AS Total
> >         ,      (time NOT LIKE '%:00') AS Late
> >         FROM   messages
> >         WHERE  date = DATE('now')
> >     )
> > )
> >
>
> And even more useful:
> SELECT date
> ,       Total
> ,       Late
> ,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> FROM    (
>     SELECT date
>     ,      SUM(total) AS Total
>     ,      SUM(late)  AS Late
>     FROM  (
>         SELECT date
>         ,       1                      AS Total
>         ,      (time NOT LIKE '%:00') AS Late
>         FROM   messages
>         WHERE  date >= DATE('now', '-7 days')
>            AND date  < DATE('now')
>     )
>     GROUP BY date
> )
> ORDER BY date DESC
>
> --
> Cecil Westerhof
> _______________________________________________
> 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: Get data in one query

Keith Medcalf
In reply to this post by Cecil Westerhof-5

... don't forget that Date('now') returns the UT1 date, not the local (as in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date in accordance with the timezone where your computer thinks it is located and should always be accurate for 'now' but maybe not a few years in the past on Operating Systems that do not contain/use a full set of UT1 -> localtime (timezone) conversion rules (such as Windows).

---
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 [mailto:sqlite-users-
>[hidden email]] On Behalf Of Cecil Westerhof
>Sent: Wednesday, 29 August, 2018 12:46
>To: SQLite mailing list
>Subject: Re: [sqlite] Get data in one query
>
>2018-08-29 18:58 GMT+02:00 Cecil Westerhof <[hidden email]>:
>
>> 2018-08-29 18:06 GMT+02:00 R Smith <[hidden email]>:
>>
>>>
>>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>>>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>>>           FROM messages
>>>          WHERE date = DATE('now')
>>>        )
>>>
>>
>> Works like a charm. Thanks.
>>
>> I made it even more useful:
>> SELECT Total
>> ,       Late
>> ,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>> FROM    (
>>     SELECT SUM(total) AS Total
>>     ,      SUM(late)  AS Late
>>     FROM  (
>>         SELECT 1                      AS Total
>>         ,      (time NOT LIKE '%:00') AS Late
>>         FROM   messages
>>         WHERE  date = DATE('now')
>>     )
>> )
>>
>
>And even more useful:
>SELECT date
>,       Total
>,       Late
>,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>FROM    (
>    SELECT date
>    ,      SUM(total) AS Total
>    ,      SUM(late)  AS Late
>    FROM  (
>        SELECT date
>        ,       1                      AS Total
>        ,      (time NOT LIKE '%:00') AS Late
>        FROM   messages
>        WHERE  date >= DATE('now', '-7 days')
>           AND date  < DATE('now')
>    )
>    GROUP BY date
>)
>ORDER BY date DESC
>
>--
>Cecil Westerhof
>_______________________________________________
>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: Get data in one query

Cecil Westerhof-5
In reply to this post by wmertens
2018-08-29 21:26 GMT+02:00 Wout Mertens <[hidden email]>:

> By the way, why not store the time as epoch? Date and time in one...
>

Because I think it is better to have date and time as different (text)
fields.

--
Cecil Westerhof
_______________________________________________
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: Get data in one query

Cecil Westerhof-5
In reply to this post by Keith Medcalf
2018-08-29 21:44 GMT+02:00 Keith Medcalf <[hidden email]>:

>
> ... don't forget that Date('now') returns the UT1 date, not the local (as
> in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local
> date in accordance with the timezone where your computer thinks it is
> located and should always be accurate for 'now' but maybe not a few years
> in the past on Operating Systems that do not contain/use a full set of UT1
> -> localtime (timezone) conversion rules (such as Windows).
>

I know and I prefer it that way. No problems with date + time when the
clock is set back. That is why I do my statistics after two in the morning.

--
Cecil Westerhof
_______________________________________________
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: Get data in one query

David Raymond
In reply to this post by Keith Medcalf
Good to know. Is that actually documented anywhere? All I see is...


https://www.sqlite.org/lang_datefunc.html
"Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use."


https://www.sqlite.org/c3ref/vfs.html
"The xCurrentTime() method returns a Julian Day Number for the current date and time as a floating point value. The xCurrentTimeInt64() method returns, as an integer, the Julian Day Number multiplied by 86400000 (the number of milliseconds in a 24-hour day). SQLite will use the xCurrentTimeInt64() method to get the current date and time if that method is available (if iVersion is 2 or greater and the function pointer is not NULL) and will fall back to xCurrentTime() if xCurrentTimeInt64() is unavailable."


Nothing seems to say "this (is/should be) in (UTC/localtime)" or even "the default vfs object gives UTC, but this is an implementation detail and should not be relied upon"

Is there any way to know what you're getting? Or to explicitly ask for one?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Keith Medcalf
Sent: Wednesday, August 29, 2018 3:45 PM
To: SQLite mailing list
Subject: Re: [sqlite] Get data in one query


... don't forget that Date('now') returns the UT1 date, not the local (as in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date in accordance with the timezone where your computer thinks it is located and should always be accurate for 'now' but maybe not a few years in the past on Operating Systems that do not contain/use a full set of UT1 -> localtime (timezone) conversion rules (such as Windows).

---
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 [mailto:sqlite-users-
>[hidden email]] On Behalf Of Cecil Westerhof
>Sent: Wednesday, 29 August, 2018 12:46
>To: SQLite mailing list
>Subject: Re: [sqlite] Get data in one query
>
>2018-08-29 18:58 GMT+02:00 Cecil Westerhof <[hidden email]>:
>
>> 2018-08-29 18:06 GMT+02:00 R Smith <[hidden email]>:
>>
>>>
>>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>>>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>>>           FROM messages
>>>          WHERE date = DATE('now')
>>>        )
>>>
>>
>> Works like a charm. Thanks.
>>
>> I made it even more useful:
>> SELECT Total
>> ,       Late
>> ,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>> FROM    (
>>     SELECT SUM(total) AS Total
>>     ,      SUM(late)  AS Late
>>     FROM  (
>>         SELECT 1                      AS Total
>>         ,      (time NOT LIKE '%:00') AS Late
>>         FROM   messages
>>         WHERE  date = DATE('now')
>>     )
>> )
>>
>
>And even more useful:
>SELECT date
>,       Total
>,       Late
>,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>FROM    (
>    SELECT date
>    ,      SUM(total) AS Total
>    ,      SUM(late)  AS Late
>    FROM  (
>        SELECT date
>        ,       1                      AS Total
>        ,      (time NOT LIKE '%:00') AS Late
>        FROM   messages
>        WHERE  date >= DATE('now', '-7 days')
>           AND date  < DATE('now')
>    )
>    GROUP BY date
>)
>ORDER BY date DESC
>
>--
>Cecil Westerhof
>_______________________________________________
>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: Get data in one query

Abroży Nieprzełoży
Julian Day is defined in terms of Universal Time.
https://en.wikipedia.org/wiki/Julian_day


2018-08-29 22:33 GMT+02:00, David Raymond <[hidden email]>:

> Good to know. Is that actually documented anywhere? All I see is...
>
>
> https://www.sqlite.org/lang_datefunc.html
> "Format 11, the string 'now', is converted into the current date and time as
> obtained from the xCurrentTime method of the sqlite3_vfs object in use."
>
>
> https://www.sqlite.org/c3ref/vfs.html
> "The xCurrentTime() method returns a Julian Day Number for the current date
> and time as a floating point value. The xCurrentTimeInt64() method returns,
> as an integer, the Julian Day Number multiplied by 86400000 (the number of
> milliseconds in a 24-hour day). SQLite will use the xCurrentTimeInt64()
> method to get the current date and time if that method is available (if
> iVersion is 2 or greater and the function pointer is not NULL) and will fall
> back to xCurrentTime() if xCurrentTimeInt64() is unavailable."
>
>
> Nothing seems to say "this (is/should be) in (UTC/localtime)" or even "the
> default vfs object gives UTC, but this is an implementation detail and
> should not be relied upon"
>
> Is there any way to know what you're getting? Or to explicitly ask for one?
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On
> Behalf Of Keith Medcalf
> Sent: Wednesday, August 29, 2018 3:45 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Get data in one query
>
>
> ... don't forget that Date('now') returns the UT1 date, not the local (as in
> Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date
> in accordance with the timezone where your computer thinks it is located and
> should always be accurate for 'now' but maybe not a few years in the past on
> Operating Systems that do not contain/use a full set of UT1 -> localtime
> (timezone) conversion rules (such as Windows).
>
> ---
> 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 [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Cecil Westerhof
>>Sent: Wednesday, 29 August, 2018 12:46
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Get data in one query
>>
>>2018-08-29 18:58 GMT+02:00 Cecil Westerhof <[hidden email]>:
>>
>>> 2018-08-29 18:06 GMT+02:00 R Smith <[hidden email]>:
>>>
>>>>
>>>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>>>>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>>>>           FROM messages
>>>>          WHERE date = DATE('now')
>>>>        )
>>>>
>>>
>>> Works like a charm. Thanks.
>>>
>>> I made it even more useful:
>>> SELECT Total
>>> ,       Late
>>> ,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>>> FROM    (
>>>     SELECT SUM(total) AS Total
>>>     ,      SUM(late)  AS Late
>>>     FROM  (
>>>         SELECT 1                      AS Total
>>>         ,      (time NOT LIKE '%:00') AS Late
>>>         FROM   messages
>>>         WHERE  date = DATE('now')
>>>     )
>>> )
>>>
>>
>>And even more useful:
>>SELECT date
>>,       Total
>>,       Late
>>,       CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>>FROM    (
>>    SELECT date
>>    ,      SUM(total) AS Total
>>    ,      SUM(late)  AS Late
>>    FROM  (
>>        SELECT date
>>        ,       1                      AS Total
>>        ,      (time NOT LIKE '%:00') AS Late
>>        FROM   messages
>>        WHERE  date >= DATE('now', '-7 days')
>>           AND date  < DATE('now')
>>    )
>>    GROUP BY date
>>)
>>ORDER BY date DESC
>>
>>--
>>Cecil Westerhof
>>_______________________________________________
>>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