Help needed to return the right math results in sql query
(1) By ThanksRyan on 2021-07-08 04:09:19 [link]
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.
(2.1) By Keith Medcalf (kmedcalf) on 2021-07-08 04:46:51 edited from 2.0 in reply to 1 [link]
You must phrase the query thusly because aliases only apply to name the projection output and do not have scope within the select clause. ``` select Counts, xz_in, zy_out, (zy_out + 82793601.0) / xz_in from ( select count(IDRecord) as Counts, sum(xz) as xz_in, sum(zy) as zy_out from EGRecord ) ; ``` Note the number added is floating point because I assume that you wish a floating point answer, not an integer answer.
(3) By ThanksRyan on 2021-07-09 19:59:35 in reply to 2.1
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?
(4.1) By ThanksRyan on 2021-07-09 20:31:17 edited from 4.0 in reply to 3 [link]
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 ) ```
(5) By Larry Brasfield (larrybr) on 2021-07-09 20:26:54 in reply to 3 [link]
> ... Do I need another sub-select or can it be within the existing one? In the spirit of gifting a fishing pole rather than fish: A set of select "projection"<sup>1</sup> expressions may use identifiers that are in scope for that select construct. The aliases you assign are not suddenly in that scope. Rather, they name the elements of output tuples from that select construct. So to answer your question yourself: Identify the select constructs, which may be nested within other select constructs or in DML where tuple set can be used to qualify operations or provide data for them. Then, for each select construct, ask: Do the projection expressions use column names exposed by the table(s) or view(s) or subselect(s) in the FROM clause? If yes, all is good. If no, you probably need another, nested subselect. (Or, you need to restructure the query with this scope rule in mind.) ---- 1. This is what Keith was telling you above. The term "<u>[projection](https://en.wikipedia.org/wiki/Projection_(relational_algebra\))</u>" is from relational algebra, part of database theory since it was invented.
(6.1) By ThanksRyan on 2021-07-09 22:01:05 edited from 6.0 in reply to 5 [link]
> 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 TID*0.003*250 ``` (TID) - (lag(TID,1,0)*0.003*250 ``` doesn't work
(7) By Larry Brasfield (larrybr) on 2021-07-09 21:53:30 in reply to 6.0 [link]
> it won't do any good ... Here is your last posted SQL in this thread, annotated:<code> select -- This is the "outer SELECT" here. EG, -- Creates a tuple element named EG as projected from this outer SELECT. [a] -- It uses well-defined projected tuple element EG from the inner SELECT. GPVBW/1000000-EG as Diff, -- Creates <i>ditto</i> named Diff as <i>ditto</i> -- It uses ... elements named EG and GPVBW from inner Select (or fails) Delay -- Creates a tuple element named Delay as projected from this outer SELECT -- It uses ... element named Delay from inner Select (or fails) -- None of the above names used in expressions (or lone terms) binds to any of -- the tuple elements projected from this outer SELECT. They can only bind to -- named tuple elements projected from the below inner SELECT. from ( select -- This is the "inner SELECT" here. \*, -- Presumably, this creates GPVBW and Delay as projected tuple elements. TID\*0.003\*250 as EG, -- Creates EG projected from this inner SELECT Diff/0.003/250/750\*60 -- Creates a projected tuple element with a poorly -- defined name which is hard to use and useless here. from CalculateGrowth ) </code> This is very fundamental to using SELECT, so I encourage you to study and understand the name scoping (or possible binding) rules in effect as noted above. ---- \[a. This is per convention; SQL language definition does not require it. \]