SQLite Forum

Timeline
Login

15 forum posts by user davidjackson

2021-10-20
18:12 Post: I need a little help with UPDATE? (artifact: e89a8dff6c user: davidjackson)

Ok, I think I am close. What is the correct code for my update query?

UPDATE t1 SET t1.start_station_name = s.start_station_name WHERE s.start_lat = t1.start_lat AND s.start_lng = t1.start_lng

TX, David

2021-09-30
04:11 Post: A little help with temp tables? (artifact: 2efe181688 user: davidjackson)

What am I missing here:

CREATE TEMP TABLE hello ( SELECT ProductId, UnitPrice, SUM(Quantity) AS Qty FROM S GROUP BY ProductId ORDER BY Qty LIMIT 5 );

TX David

2021-08-04
21:05 Reply: Calculating Trip Duration in Minutes? (artifact: eaaf9285d0 user: davidjackson)

Ryan, Thanks for your reply.

David

21:05 Reply: Calculating Trip Duration in Minutes? (artifact: 677a8bdd5d user: davidjackson)

Larry, Thanks for you reply.

David

19:35 Post: Calculating Trip Duration in Minutes? (artifact: 29d741eb45 user: davidjackson)

First A big thanks to Richard Hipp for the answer my original trip duration post. One small clarification, If I want to calculate trip duration in minutes would I use something like this:

CREATE TABLE detail AS SELECT start_lat,start_lng, rideable_type, member_casual, started_at,ended_at, (julianday(ended_at)-julianday(started_at))*3600 as duration FROM t1 WHERE duration >0

TX, David

2021-07-28
22:58 Post: Calculating Trip Duration (artifact: 095dd2858e user: davidjackson)

How can I use a case statement to calculate trip duration?

end_datetime - start_endtime = trip duration. If the trip duration is less than 0 then I want to do: start_endtime - end_datetime = trip duration.

Here is the code I'm trying to use:

Select case when datediff(minute, started_at, ended_at) < 0 then datediff(minute, ended_at, started_at) else datediff(minute, started_at, ended_at) end

FROM detail

Thanks, David

2021-07-26
20:25 Post: How do I do a one - many join? (artifact: 4a637b9f22 user: davidjackson)

How am trying to combine tables stations and detail. I want to use the longitude and latitude files for the join. I want all the records from stations and all the matching records from detail. Here is the SQL I am trying to run:

SELECT s.station_name,s.start_lat,s.start_lng,started_at,ended_at,duration FROM stations AS s,detail AS d WHERE s.start_lat = d.start_lat AND WHERE s.end_lat = d.ended_at

TX, David

2021-07-22
22:58 Reply: How can I calculate trip duration? (artifact: 46fe1ef716 user: davidjackson)

Richard, Thank look like what I am looking for.

22:00 Post: How can I calculate trip duration? (artifact: 9c52ea6756 user: davidjackson)

Howdy, Considering that SQLite doesn't seem to have a data or DateTime column type. How can I calculate trip duration? The data as a started_at and ended_at timestamps. Can I use a CAST function ie SELECT CAST("2017-08-29" AS DATE);?

TIA, David

2021-02-16
02:10 Post: How do I create a new table with select? (artifact: 6006b99cfc user: davidjackson)

Howdy,

Here is what I came up with from the web:

SELECT state,date, new_cases, new_deaths INTO Ohio FROM STATESDAILY WHERE state="Ohio";

TX, David

2020-12-06
22:21 Reply: Substract Yesterday Cases from today's Cases (artifact: 0f0b57d47c user: davidjackson)

How about something like this:

SELECT date, confirmed, confirmed - LAG ( confirmed, 1, 0 ) OVER ( ORDER BY date ) DailyCases FROM JHUDATA where country = "US"

2020-12-05
23:40 Post: Substract Yesterday Cases from today's Cases (artifact: 707e138b62 user: davidjackson)
My table looks like this: Date geoID Cases Deaths. What I'm want to do is substract yesterday's Case from today's Cases. I came across code for LAG() function online. Here is the code I tried to modify

/* Calculate daily totals Source: https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-lag/ */ SELECT geoID, Reported, Cases, LAG ( Cases, 1, 0 ) OVER ( ORDER BY Reported ) PreviousTotalCases FROM ECDC WHERE geoID ="US";

2020-12-04
22:00 Reply: How can I calc weekly totals? (artifact: 74d917cf2f user: davidjackson)

Keith, Thanks that was exactly what I was looking for.

18:27 Reply: How can I calc weekly totals? (artifact: 91689aef4f user: davidjackson)

Thanks, I give it a try. David

02:27 Post: How can I calc weekly totals? (artifact: 8bcaec2950 user: davidjackson)

My covid table contains one row for each day, Each row contains looks like this: date,cases, deaths. I want to calc the weekly totals for cases and deaths. The query result would have the Sunday YMD, TotalCases and TotaldDeaths. TIA, David