SQLite Forum

Timeline
Login

50 most recent forum posts by user ThanksRyan

2021-12-30
17:32 Post: Consider SQLite - an article (artifact: 5e9ec97c03 user: ThanksRyan)

Hi,

Many of you may have seen this article by SQLite friend Wesley: Consider SQLite

Lobsters discussion and the hacker news discussion.

I'm excited to see where and how SQLite will be used in 2022 by friends on this forum, and by those who don't yet know about SQLite to solve their problems.

Richard and team, thank you for SQLite. It's a blessing for the world.

2021-09-17
17:30 Reply: Javascript enforcement (artifact: 685bbb57bb user: ThanksRyan)

That isn't very good if the user is using the keyboard only.

You can also download from here: https://github.com/sqlite/sqlite/releases

Does github require javascript to download from that page?

Many users including myself normally disable JavaScripts.

Congratulations. That may mean the page you want to see for SQLite won't load.

An example of how the description in the <noscript> block might be:

How long as the current message been in place? Does it need changing at this point? Those who purposefully disable javascript in modern web browsers very likely know that doing so means various webpages won't load correctly for them already. Did you know that a birthing person (like, you know, a female) drinking alcohol is not advisable?

2021-09-15
13:39 Post: SQLite blessing anniversary (artifact: bb6a414c91 user: ThanksRyan)

It was 20 years ago today that drh gave us his blessing:

**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.

Three simple statements that are difficult for me to do every day. To all those who I have let down and disappointed over the last twenty years: please forgive me.

May our next twenty years come easier to live by this blessing.

2021-08-04
20:18 Edit reply: Calculating Trip Duration in Minutes? (artifact: 54e89e7481 user: ThanksRyan)

(julianday(ended_at)-julianday(started_at))*3600

I do the number of minutes in a day: 1440. If it's less than a day, you'll get a decimal number less than 1.

19:55 Reply: Help calculating data when answers in previous row (artifact: 79366910ff user: ThanksRyan)

Rico,

This is beautiful. Thank you for your help and hand-holding through this exercise.

19:54 Reply: Calculating Trip Duration in Minutes? (artifact: 08541ae9e9 user: ThanksRyan)

(julianday(ended_at)-julianday(started_at))*3600

I do the number of minutes in a day: 1440. If it's less than a day, you'll get a decimal number.

2021-07-29
13:27 Reply: Help calculating data when answers in previous row (artifact: 0aa991dfae user: ThanksRyan)

A simpler question that may lead me on to the next path:

How can I sum the winAmount and count(betAmount) for a given betAmount?

Do I need to use window/lag functions for this?

06:00 Post: Help calculating data when answers in previous row (artifact: 04a95958d2 user: ThanksRyan)

Hi,

Question:

I would like to calculate the winAmount and POP for each of the betAmount values, but some of the data is only available in the 'pre purchase bal' and rest is available in 'completed final bal'.

Sample data:

event,betAmount,winAmount,endBalance
Purchase Started Pre Purchase Balance,0.25,,
Game Completed Final Balance,,0.0,299.75
Purchase Started Pre Purchase Balance,0.25,,
Game Completed Final Balance,,0.5,300.0
Purchase Started Pre Purchase Balance,0.25,,
Game Completed Final Balance,,0.0,299.75
Purchase Started Pre Purchase Balance,1.0,,
Game Completed Final Balance,,0.6,299.35
Purchase Started Pre Purchase Balance,1.0,,
Game Completed Final Balance,,0.2,298.55
Purchase Started Pre Purchase Balance,1.0,,
Game Completed Final Balance,,0.6,298.15

Query:

 SELECT purchasesbyba,
       wager,
       betamount,
       purchasesbyba,
       winamount,
       (winamount+ pw) / wager * 100.0 AS pop
FROM   (
                SELECT   event,
                         count(
                         CASE
                                  WHEN(
                                                    event IS 'Game Completed Final Balance') THEN 1
                                  ELSE NULL
                         END)                    AS 'purchases',
                         sum(mga.betamount)      AS wager,
                         sum(mga.winamount)      AS winamount,
                         sum(mga.progressivewin) AS pw,
                         count(
                         CASE
                                  WHEN(
                                                    event IS 'Game Completed Final Balance'
                                           AND      winamount != 0.0 ) THEN 1
                                  ELSE NULL
                         END) AS wins,
                         betamount,
                         count(betamount) purchasesbyba
                FROM     mgaccounting     AS mga
                GROUP BY betamount ); 

Results so far:

purchasesbyba,wager,betamount,purchasesbyba,winamount,pop
0,,,0,6637.20000000004,
2236,559.0,0.25,2236,,
1141,570.5,0.5,1141,,
1316,1316.0,1.0,1316,,
1278,1917.0,1.5,1278,,
1123,2807.5,2.5,1123,,

Thank you for any pointers!

2021-07-23
04:22 Reply: How can I calculate trip duration? (artifact: 88a9e1d4eb user: ThanksRyan)

I think this is simpler and doesn't need a subsub query.

How many transactions per minute...

select
        Purchases,
	duration*1440,
	purchases/(duration*1440) as TxnPerMin
from ( 
       select
	event,
	count(event) as Purchases,
	julianday(max(`datetime`)) - julianday(min(`datetime`)) as duration
from table1
where event ='end'
);
Purchases,duration*1440,TxnPerMin
42767,1884.12331692874,22.6986204224219
22.6986204224219*1884.12331692874
2021-07-22
22:38 Reply: How can I calculate trip duration? (artifact: 565d04a2b1 user: ThanksRyan)

Similar question for me. I think I'm close but still not there yet...

I want to know the transactions per minute based on the elapsed time:

select
     purchases,
     Duration*24*60 as minutes,
     TxnPerMin
from ( 
          select
	event,
	count(event) as Purchases,
	julianday(max(`datetime`)) - julianday(min(`datetime`)) as duration,
	minutes/purchases as TxnPerMin
from table1
	where event ='end'
);

What kind of subquery do I need for this?

22:34 Reply: How can I calculate trip duration? (artifact: 0a6f6e8aa0 user: ThanksRyan)
2021-07-19
17:46 Reply: Inserting One Billion Rows in SQLite Under A Minute (artifact: 1bf79b031c user: ThanksRyan)
17:44 Reply: Inserting One Billion Rows in SQLite Under A Minute (artifact: b8770e83b1 user: ThanksRyan)

Avi's SQLite thread from May when he asked about this:

https://sqlite.org/forum/forumpost/22f19295620387a1?t=h

15:22 Reply: Tips for getting SQLite under Python to perform better with more RAM? (artifact: da324cec56 user: ThanksRyan)

...which means I don't have to spend any money on hosting my applications unless they are actively serving traffic.

Do you have a blog post on how you've managed this?

...

unrelated to this thread (mostly)

https://news.ycombinator.com/item?id=27871574

You may want to check out Visidata and see how the data is loaded. I doubt it's being imported into a SQLite database, but it's python code that can load csv files fast or at least in the background. https://www.visidata.org

2021-07-15
22:24 Reply: More math help and what I've learned (artifact: e9f8970374 user: ThanksRyan)

Hello MBL! My goodness, that is so awesome. Thank you for assisting and showing me the else usage. I didn't consider it or know about it.

This now gets me the diff column correctly! The very minor thing that was aggravating was realizing I needed to do 1000000.0, otherwise the values were 0.25 off.

SELECT *,
       CASE
           WHEN previous_transId IS NULL THEN growthpool/1000000.0-EstGrowth
           ELSE (growthpool-Previous_GCOV)/1000000-EstGrowth
       END AS diff
FROM
  (SELECT GrowthPool,
          TransactionId AS current_transId,
          lag(GrowthCarry) OVER w Previous_GCOV,
                                GrowthCarry AS Current_GCOV,
                                (lag(GrowthCarry) OVER w) AS GCOVAW,
                                lag(TransactionId) OVER w AS previous_transId,
                                                        CASE
                                                            WHEN lag(TransactionId, 1) OVER w THEN ((TransactionId) - (lag(TransactionId) OVER w))*0.003*250
                                                            ELSE TransactionId *0.003*250
                                                        END AS EstGrowth
   FROM CalculateGrowth2 WINDOW w AS (PARTITION BY PIG
                                      ORDER BY PIG)) e
WHERE EstGrowth IS NOT NULL

Thank you for your help!

15:12 Reply: More math help and what I've learned (artifact: 2f0e851a25 user: ThanksRyan)

Is my request unsolvable in SQL?

Re-stating it:

The very first record needs a different way to calculate EstGrowth and the diff than all the other following records.

Very first record for EstGrowth needs this calculation:

TransactionId*0.003*250

Very first record for diff needs this calculation:

growthpool/1000000-EstGrowth

Thanks for any pointers!

2021-07-13
23:39 Reply: What join is needed for non-similar data? (artifact: a9fb29aa9c user: ThanksRyan)

Table1:

CREATE TABLE IF NOT EXISTS "table1" (
    number int,
    wager int,
    PAIC int

);

Table2:

CREATE TABLE IF NOT EXISTS "table2" (
    WinRecord int,
    ClaimAmt int
);

Some sample data for table1:

INSERT INTO table1(number,wager,PAIC) VALUES(1,1065,1);
INSERT INTO table1(number,wager,PAIC) VALUES(2,375,2);
INSERT INTO table1(number,wager,PAIC) VALUES(3,405,3);
INSERT INTO table1(number,wager,PAIC) VALUES(4,285,4);
INSERT INTO table1(number,wager,PAIC) VALUES(5,450,5);

Sample data for table2:

INSERT INTO table2(WinRecord,ClaimAmt) VALUES(1,32);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(2,32);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(3,32);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(4,40);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(5,40);

Your query:

select counts, 
      wager, 
       paic, 
       (paic + PW) / wager * 100.0 as POP 
  from ( 
          select count(e.number) as counts, 
                 sum(e.wager) as wager, 
                 sum(e.paic) as paic, 
                 sum(table2.ClaimAmt) as PW
            from table1
        group by counts
       ) as e
  join table2
    on e.counts = table2.winRecord

I guess my assumption about table alias' are wrong. I get no such column on e.number

This kind doesn't have an error but POP is wrong:

select counts, 
       counts, 
       wager, 
       (PAIC+ PW) / wager * 100.0 as POP 
  from ( 
          select count(number) as counts, 
                 sum(wager) as wager, 
                 sum(PAIC) as PAIC,
                 sum(table2.ClaimAmt) as PW 
from
	table1
left outer join table2
on number = table2.ClaimAmt
)
23:15 Edit reply: More math help and what I've learned (artifact: ebabc323c5 user: ThanksRyan)

hmmm

The next record, with the real value is even further off than before.

┌───────────────┬────────────┬──────────────────┬─────────────────┬───────────┬───────────────┬──────────────┬────────┬──────┐
│ TransactionId │ GrowthPool │ previous_transId │ current_transId │ EstGrowth │ Previous_GCOV │ Current_GCOV │ GCOVAW │ Diff │
├───────────────┼────────────┼──────────────────┼─────────────────┼───────────┼───────────────┼──────────────┼────────┼──────┤
│ 0             │ 0          │                  │ 0               │ -0.75     │               │ 0            │        │      │
│ 464           │ 269250000  │                  │ 464             │ 347.25    │               │ 250000       │        │      │
└───────────────┴────────────┴──────────────────┴─────────────────┴───────────┴───────────────┴──────────────┴────────┴──────┘

edit..actually the estgrowth is the same as mine.

23:14 Edit: What join is needed for non-similar data? (artifact: 3f841d4192 user: ThanksRyan)

Hi all,

Previous answer from Keith: forumpost:f75c5.

The value 82793601.0 is from a different table that I've now imported into SQLIte and I'd like to know how to calculate it within sqlite, but the two tables don't have the same schema. Some data is shared among it, but the column names are different.

I don't know what kind of join I need but I know this doesn't work.

select
	counts,
	xz_in,
	zy_out,
	(zy + PW) / coin_in * 100.0 as POP
from (
	select
	count(e.IDRecord) as counts,
	sum(e.wager) as xz_in,
	sum(e.zy) as zy_out,
	sum(cg.ClaimAmt) as PW
from
	table1 as e
join cg on
	e.count = cg.winRecordId
)

The result:

414919861,103729965250,93239178150,9700.0

Should actually be:

4342056,1085514000,976264100,94.0102679467976

(976264100 + 44230520) / 1085514000 * 100

Note: 82793601.0 was the value in the previous post, but there's now a new value and it's correct above, 976264100.

Do I want a cross join in this case?

22:28 Reply: More math help and what I've learned (artifact: e909370ff2 user: ThanksRyan)

hmmm

The next record, with the real value is even further off than before.

┌───────────────┬────────────┬──────────────────┬─────────────────┬───────────┬───────────────┬──────────────┬────────┬──────┐
│ TransactionId │ GrowthPool │ previous_transId │ current_transId │ EstGrowth │ Previous_GCOV │ Current_GCOV │ GCOVAW │ Diff │
├───────────────┼────────────┼──────────────────┼─────────────────┼───────────┼───────────────┼──────────────┼────────┼──────┤
│ 0             │ 0          │                  │ 0               │ -0.75     │               │ 0            │        │      │
│ 464           │ 269250000  │                  │ 464             │ 347.25    │               │ 250000       │        │      │
└───────────────┴────────────┴──────────────────┴─────────────────┴───────────┴───────────────┴──────────────┴────────┴──────┘
20:31 Post: More math help and what I've learned (artifact: cb343cb7d8 user: ThanksRyan)

Hi,

Thanks for all the help you friends have provided in my silliness to get the right answers from my data. I appreciate the time each and one of you take to help out.

the table:

CREATE TABLE "calculategrowth" (
"transactionId" int,
"pig" int,
"growthpool" int,
"GrowthCarry" int
);

sample data:

INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(464,1000065,269250000,250000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(645,1000065,134500000,1500000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(3880,1000065,2430000000,0);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(9668,1000065,4338750000,750000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(9949,1000065,210750000,1750000);

Almost working query:

SELECT *,
	(GrowthPool-GCOVAW)/1000000-EstGrowth as Diff
FROM (
  SELECT
	TransactionId,
	GrowthPool,

	lag(TransactionId,1) OVER
	w previous_transId, TransactionId as current_transId,
	((TransactionId) -  (lag(TransactionId,1,1) OVER w))*0.003*250 EstGrowth,

	lag(GrowthCarry) OVER
	w Previous_GCOV, GrowthCarry as Current_GCOV,
	(lag(GrowthCarry) OVER w) as GCOVAW

  FROM CalculateGrowth
  WINDOW w AS (PARTITION BY PIG ORDER BY PIG)
) e
WHERE EstGrowth IS NOT NULL;

Results:

┌───────────────┬────────────┬──────────────────┬─────────────────┬───────────┬───────────────┬──────────────┬─────────┬───────┐
│ TransactionId │ GrowthPool │ previous_transId │ current_transId │ EstGrowth │ Previous_GCOV │ Current_GCOV │ GCOVAW  │ Diff  │
├───────────────┼────────────┼──────────────────┼─────────────────┼───────────┼───────────────┼──────────────┼─────────┼───────┤
│ 464           │ 269250000  │                  │ 464             │ 347.25    │               │ 250000       │         │       │
│ 645           │ 134500000  │ 464              │ 645             │ 135.75    │ 250000        │ 1500000      │ 250000  │ -1.75 │
│ 3880          │ 2430000000 │ 645              │ 3880            │ 2426.25   │ 1500000       │ 0            │ 1500000 │ 1.75  │
│ 9668          │ 4338750000 │ 3880             │ 9668            │ 4341.0    │ 0             │ 750000       │ 0       │ -3.0  │
│ 9949          │ 210750000  │ 9668             │ 9949            │ 210.75    │ 750000        │ 1750000      │ 750000  │ -0.75 │
└───────────────┴────────────┴──────────────────┴─────────────────┴───────────┴───────────────┴──────────────┴─────────┴───────┘

Problem:

Since the very first record has no previous record to compare against, it needs a different formula for it.

EstGrowth: TransactionId*0.003*250 for 348.00. You will notice mine is close, 347.25

Also, since the diff is incorrect on the first one, the rest of the records are also wrong. The first needs this: GrowthPool-EstGrowth, which is -78.75

Expected results:

TransactionId,pig,GrowthPool,GrowthCarry,EstGrowth,Diff
464,1000065,269250000,250000,348,-78.75
645,1000065,134500000,1500000,135.75,-1.5
3880,1000065,2430000000,0,2426.25,2.25
9668,1000065,4338750000,750000,4341,-2.25
9949,1000065,210750000,1750000,210.75,-0.75

What needs to change to target the very first record? Do I need a where in a nested select or something?

Thanks for the teaching!

00:00 Post: What join is needed for non-similar data? (artifact: 141191e8b4 user: ThanksRyan)

Hi all,

Previous answer from Keith: forumpost:f75c5.

The value 82793601.0 is from a different table that I've now imported into SQLIte and I'd like to know how to calculate it within sqlite, but the two tables don't have the same schema. Some data is shared among it, but the column names are different.

I don't know what kind of join I need but I know this doesn't work.

select counts, xz_in, zy_out, (zy + PW) / coin_in * 100.0 as POP from ( select count(e.IDRecord) as counts, sum(e.wager) as xz_in, sum(e.zy) as zy_out, sum(cg.ClaimAmt) as PW from table1 as e join cg on e.count = cg.winRecordId )

The result:

414919861,103729965250,93239178150,9700.0

Should actually be:

4342056,1085514000,976264100,94.0102679467976

(976264100 + 44230520) / 1085514000 * 100

Note: 82793601.0 was the value in the previous post, but there's now a new value and it's correct above, 976264100.

Do I want a cross join in this case?

2021-07-12
23:58 Post: (Deleted) (artifact: 3de02a72a2 user: ThanksRyan)

Hi all,

Previous answer from Keith: forumpost:f75c5.

The value 82793601.0 is from a different table that I've now imported into SQLIte and I'd like to know how to calculate it within sqlite, but the two tables don't have the same schema. Some data is shared among it, but the column names are different.

I don't know what kind of join I need but I know this doesn't work.

select
	counts,
	xz_in,
	zy_out,
	(zy + PW) / coin_in * 100.0 as POP
from (
	select
	count(e.IDRecord) as counts,
	sum(e.wager) as xz_in,
	sum(e.zy) as zy_out,
	sum(cg.ClaimAmt) as PW
from
	table1 as e
join cg on
	e.count = cg.winRecordId
)

The result:

414919861,103729965250,93239178150,9700.0

Should actually be:

4342056,1085514000,976264100,94.0102679467976

(976264100 + 44230520) / 1085514000 * 100

Note: 82793601.0 was the value in the previous post, but there's now a new value and it's correct above, 976264100.

2021-07-09
22:01 Edit reply: Help needed to return the right math results in sql query (artifact: c1154cf470 user: ThanksRyan)

In the spirit of gifting a fishing pole rather than fish

Thanks. Although, it won't do any good. I'm floating in outer space and there's no water in sight.

It turns out the query turns out to be much more complicated than I thought.

For the next value in the data, I need to subtract from previous and then do the multiplication to get the EG value.

SELECT *
 FROM (
  SELECT
	lag(TID, 1,0) OVER
	w current_TID, TID as next_TID,
	(TID) - (lag(TID,1,0) OVER w) EG
  FROM CalculateGrowth
  WINDOW w AS (PARTITION BY PIG ORDER BY PIG)
) e
WHERE EG IS NOT NULL;

Not quite sure, yet, where to fit in the difference between the TID0.003250

(TID) - (lag(TID,1,0)*0.003*250

doesn't work

21:12 Reply: Help needed to return the right math results in sql query (artifact: bf3e01d2d8 user: ThanksRyan)

In the spirit of gifting a fishing pole rather than fish

Thanks. Although, it won't do any good. I'm floating in outer space and there's no water in sight.

20:31 Edit reply: Help needed to return the right math results in sql query (artifact: b64f807c99 user: ThanksRyan)

That may not be very clear.

This is what I'm trying...

select
	EG,
GPVBW/1000000-EG as Diff,
    Delay
from (
	select
		*,
		TID*0.003*250 as EG,
		Diff/0.003/250/750*60
from CalculateGrowth
)
20:13 Reply: Help needed to return the right math results in sql query (artifact: 5efee309fb user: ThanksRyan)

That may not be very clear.

This is what I'm trying...

select
	EG,
GPVBW/1000000-WG as Diff,
    Delay
from (
	select
		*,
		TID*0.003*250 as EG,
		Diff/0.003/250/750*60
from CalculateGrowth
)
19:59 Reply: Help needed to return the right math results in sql query (artifact: ea71234e28 user: ThanksRyan)

Different database, different data, but same end result: use alias to calculate results.

This works but it's probably ugly

select
	EG,
GPVBW/1000000-WG as Diff
from (
	select
		*,
		TID*0.003*250 as EG
		
from CalculateGrowth
)


But then I want to do another calculation based in the Diff column above:

Diff//0.003/250/750*60 as Delay

Do I need another sub-select or can it be within the existing one?

2021-07-08
13:53 Reply: Awesome people (artifact: 883719b720 user: ThanksRyan)

Without a doubt they're humble, kind, helpful, and even give the right snarky answers at exactly the right time.

04:09 Post: Help needed to return the right math results in sql query (artifact: 8a07482a9d user: ThanksRyan)

Hi,

In a query, I want SQLite to get records for three results, and on the fourth, I want to plug in a number manually and have SQLite calculate the results.

select
	count(IDRecord) as Counts,
	sum(xz) as xz_in,
	sum(zy) as zy_out,
	sum((zy_out)+82793601)/sum(xz_in)
from
	EGRRecord

Counts: 7574623

xz_in: 1893655750

zy_out: 1709334200

SQLite returns results 331175 for the result on the fourth field. Why? What does it think I'm asking for?

Is it possible to use the alias', like I am above? When I do so, SQLite says no column.

2021-07-06
20:18 Reply: Query Detail Table (artifact: 04affd0fb2 user: ThanksRyan)

Maybe what you want are views.

2021-07-05
17:18 Reply: Curious about check-in [5e7e0d4ef8] (artifact: 52900b121f user: ThanksRyan)

Thank you for your efforts on the desire to improve on what is already a very cool feature of SQLite.

2021-05-17
17:29 Reply: Help calculating percentage in SQL (artifact: 650caab663 user: ThanksRyan)

Thank you both! This works without any problem.

03:09 Edit: Help calculating percentage in SQL (artifact: 2c9c9544c7 user: ThanksRyan)

Hi,

I would like to know the percentage of incidents that happen based on the day of week (DoW).

Does the query need to sum all incidents, then filter based on DoW to divide part/whole*100.0 ? If so, how would I go about doing that?

schema:

CREATE TABLE trafficdata (
            EventID TEXT NOT NULL UNIQUE,
            EventDate TEXT,
            EventTime TEXT,
            EventCode TEXT,
            EventDescription TEXT,
            EventAddress TEXT
);

query:

select
    count(EventCode) as Incidents,
     substr('SunMonTueWedThuFriSat', 1 + 3*strftime('%w', EventDate), 3) as DoW
from
   trafficdata2
where  
   EventDate between ('2021-04-10')
	and ('2021-04-19')
	and EventCode in ('401','401A','401B','401C') 
group by 
	DoW
order by
   Incidents 
desc

results:

Incidents,DoW
2,Sun
1,Thu
1,Sat
1,Mon
1,Fri

sample data:

INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('1','2021-04-10','10:28:17 PM','401B','ACCIDENT (WITH INJURY)','5075 E WASHINGTON AVE');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('2','2021-04-11','1:29:07 AM','401','ACCIDENT (TRAFFIC)','3700 STEWART AVE');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('3','2021-04-12','12:41:19 AM','438','TRAFFIC PROBLEM','2896 S LAS VEGAS BLVD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('4','2021-04-13','12:12:07 AM','402','FIRE','3955 UNIVERSITY CENTER DR');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('5','2021-04-14','12:08:02 AM','438','TRAFFIC PROBLEM','W OAKEY BLVD / BELCASTRO ST');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('6','2021-04-15','12:11:29 AM','401C','ACCIDENT (PRIVATE PROPERTY)','3700 E BONANZA RD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('7','2021-04-16','12:11:37 AM','401','ACCIDENT (TRAFFIC)','TRAILWOOD DR / CRESTDALE LN');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('8','2021-04-17','12:13:06 AM','402','FIRE','E CRAIG RD / N LAMB BLVD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('9','2021-04-18','12:10:28 AM','401','ACCIDENT (TRAFFIC)','BOULDER HWY / E DESERT INN RD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('10','2021-04-19','12:54:32 AM','401B','ACCIDENT (WITH INJURY)','N RANCHO DR / W ALEXANDER RD');

Any pointers would be greatly appreciated!

2021-05-16
19:47 Post: Help calculating percentage in SQL (artifact: c04b483e15 user: ThanksRyan)

Hi,

I would like to know the percentage of incidents that happen based on the day of week (DoW).

Does the query need to sum all incidents, then filter based on DoW to divide part/whole*100.0 ? If so, how would I go about doing that?

schema:

CREATE TABLE trafficdata (
            EventID TEXT NOT NULL UNIQUE,
            EventDate TEXT,
            EventTime TEXT,
            EventCode TEXT,
            EventDescription TEXT,
            EventAddress TEXT
);

query:

select
    count(EventCode) as Incidents,
     substr('SunMonTueWedThuFriSat', 1 + 3*strftime('%w', EventDate), 3) as DoW
from
   trafficdata2
where  
   EventDate between ('2021-04-10')
	and ('2021-04-19')
	and EventCode in ('401','401A','401B','401C') 
group by 
	DoW
order by
   Incidents 
desc

results:

Incidents,DoW
2,Sun
1,Thu
1,Sat
1,Mon
1,Fri

sample data:

INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('1','2021-04-10','10:28:17 PM','401B','ACCIDENT (WITH INJURY)','5075 E WASHINGTON AVE');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('2','2021-04-11','1:29:07 AM','401','ACCIDENT (TRAFFIC)','3700 STEWART AVE');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('3','2021-04-12','12:41:19 AM','438','TRAFFIC PROBLEM','2896 S LAS VEGAS BLVD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('4','2021-04-13','12:12:07 AM','402','FIRE','3955 UNIVERSITY CENTER DR');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('5','2021-04-14','12:08:02 AM','438','TRAFFIC PROBLEM','W OAKEY BLVD / BELCASTRO ST');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('6','2021-04-15','12:11:29 AM','401C','ACCIDENT (PRIVATE PROPERTY)','3700 E BONANZA RD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('7','2021-04-16','12:11:37 AM','401','ACCIDENT (TRAFFIC)','TRAILWOOD DR / CRESTDALE LN');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('8','2021-04-17','12:13:06 AM','402','FIRE','E CRAIG RD / N LAMB BLVD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('9','2021-04-18','12:10:28 AM','401','ACCIDENT (TRAFFIC)','BOULDER HWY / E DESERT INN RD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('10','2021-04-19','12:54:32 AM','401B','ACCIDENT (WITH INJURY)','N RANCHO DR / W ALEXANDER RD');

Any pointers would be greatly appreciated!

2021-05-15
00:30 Reply: MySQL (artifact: e01ead16ca user: ThanksRyan)
2021-05-05
05:05 Reply: Columns metadata is invalit for RETURNING clause (artifact: 5933adc3dd user: ThanksRyan)

Hi,

Have you tried SQLite version 3.35.4? It seems some RETURNING things were fixed there:

https://sqlite.org/releaselog/3_35_4.html

(By the way, 3.35.5 is the latest release.)

2021-05-04
03:35 Reply: cli: .mode insert - use current table name (artifact: c95180f181 user: ThanksRyan)

Excellent explanation. Thank you!

2021-05-03
18:10 Reply: cli: .mode insert - use current table name (artifact: 83aa6447cf user: ThanksRyan)

Larry,

Very nice!!

It was my mistake for not attempting funwithsqlite as the argument. I mistakenly thought it would duplicate the data or override the existing table somehow.

Thanks for the fast response!

17:55 Post: cli: .mode insert - use current table name (artifact: 4f0efe386e user: ThanksRyan)

Hello,

I really like the .mode insert when using the command line interface, so that you can give a sample data that can be easily inserted into an already known schema to someone else's database.

However, when you don't specify new_table, it defaults to table in the output.

Is it possible to have the insert mode print the current table you're selecting from?

 CREATE TABLE IF NOT EXISTS
"funwithsqlite" (
 "ID" ,
 "heading2",
 "heading3"
); 
sqlite> .mode insert

sqlite> select * from funwithsqlite limit 5;
INSERT INTO "table" VALUES(2,6400,'2021-05-07');
INSERT INTO "table" VALUES(3,9700,'2021-05-10');
INSERT INTO "table" VALUES(4,6500,'2021-05-08');
INSERT INTO "table" VALUES(5,1400,'2021-05-12');
INSERT INTO "table" VALUES(6,7200,'2021-05-13');

Can table above be replaced with funwithsqlite?

If not, I respectfully request this as a feature request for the cli.

I see the .dump uses the current table name, but it looks like it dumps the entire database and not a partial amount.

Thanks!

2021-05-02
16:47 Post: How Litestream Eliminated My Database Server for $0.03/month (artifact: dbf17c7f12 user: ThanksRyan)

Not my article:

The author is using SQLite, Litestream, and Docker to replicate databases.

How Litestream Eliminated My Database Server for $0.03/month

Lobsters comments

hackernews comments

2021-04-29
16:38 Reply: WAL files deleted (artifact: 5cbc6fde43 user: ThanksRyan)

Oh, good. There's only 594 files changed since 3.28.0 and 3.35.5:

https://www.sqlite.org/src/vdiff?from=884b4b7e502b4e99&to=1b256d97b553a961&diff=0

To the Fossil hackers: it would be nice to see a count of files changed. Maybe just below the context stuff and above the the diffs.

16:28 Reply: Version 3.35.6 ?? (artifact: 3af5ddeeb5 user: ThanksRyan)

You go above and beyond everything you do, even your explanations. Thank you for continuing to maintain and improve all of your projects. We all appreciate your hard work.

02:15 Reply: SQLite database in space? (artifact: ea7acf56bc user: ThanksRyan)

Well we know it's on some type of aircraft, and aircraft are in a kind of space...30,000+ feet above sea level.

02:12 Reply: Version 3.35.6 ?? (artifact: 84935f4129 user: ThanksRyan)

There have been some recent enhancements to the dbsqlfuzz fuzzer

I was wondering what that was. I didn't know if it was something you and your team were running or external folks. Thanks for a link to the doc about it.

What exactly is this thing that looks like a hash: https://www.sqlite.org/src/info/ff3538ae37a02f4f

dbsqlfuzz 683913e98f54fe4f14e8dd11a48011f73bdca58d

Is that a private Fossil repo artifact/check-in?

Just curious...

2021-04-24
20:13 Reply: Sending output to a pipe (artifact: a8ea57bf20 user: ThanksRyan)

I imagine others might expect the same behaviour I did, though, so perhaps it could deserve a mention in the documentation.

What shall the documentation say?

$ sqlite3 mydatabase.db | less This doesn't wait for me to enter some commands before running less. Is this a problem with SQLite?

2021-04-22
16:23 Reply: Bug report: reachable assertion in zipfile module (artifact: 1bb0b14b83 user: ThanksRyan)
2021-04-20
14:38 Reply: Why no release announcement in this Forum anymore? (artifact: 09733bb9d4 user: ThanksRyan)

Should I start putting an announcement in the Forum too?

Only if you don't mind approving posts and potentially using it as a bug tracking post.

2021-04-18
21:35 Reply: SQL query help for between times and grouping times (artifact: c397056b70 user: ThanksRyan)

Hi Ryan,

Can you see why I made my username "ThanksRyan"? Your ability to, not only craft the CTE, but explain it with the detail you did is truly appreciative. This community, because of the kind of person drh and his team are, fosters humble, helpful, talented folks.

Thank you for being a member here and patiently contributing to help people better understand SQL and SQLite.

21:25 Reply: SQL query help for between times and grouping times (artifact: cfdc547999 user: ThanksRyan)

Thank you for your help explaining what you created and how it works. Your contributions on this forum (and previously the mailing list) don't go unnoticed by the members and myself. Your contributions are outstanding.

Keep up the excellent work.

A couple people have mentioned it would be better if the date & time were combined into one column. Do you think that's generally good advice if the source isn't in ISO8601? What advantages would there be?

As it is now, I can find all events based just on EventDate without a worry about the timestamp:

sqlite> select EventDate, count(*) from trafficdata group by EventDate;
┌───────────┬──────────┐
│ EventDate │ count(*) │
├───────────┼──────────┤
│ 4/10/2021 │ 13       │
│ 4/11/2021 │ 86       │
│ 4/12/2021 │ 113      │
│ 4/13/2021 │ 135      │
│ 4/14/2021 │ 112      │
│ 4/15/2021 │ 111      │
│ 4/16/2021 │ 126      │
│ 4/17/2021 │ 103      │
│ 4/18/2021 │ 30       │
└───────────┴──────────┘

If they're combined, the same query is useless:

sqlite> select EventDate, count(*) from trafficdata group by EventDate limit 5;
┌───────────────────────┬──────────┐
│       EventDate       │ count(*) │
├───────────────────────┼──────────┤
│ 4/10/2021 10:02:08 PM │ 1        │
│ 4/10/2021 10:28:17 PM │ 1        │
│ 4/10/2021 10:56:22 PM │ 1        │
│ 4/10/2021 11:00:00 PM │ 1        │
│ 4/10/2021 11:58:33 PM │ 1        │
└───────────────────────┴──────────┘
More ↓