I am running a query with SQLite and am relatively new to this. Glance of my dataset: I want group the results by Col A and then show 3 more columns (mild, moderate, high) and their counts — Here is what I’m looking for: I know how I can select just one of the columns (say, mild), but not sure
Tag: sqlite
Get comments per video and total sum of comments per video channel
In addition to Get total sum of video comments for each video I noticed that I can’t use v.commentCount because I cleaned some data and now the column is inaccurate. So I need to count the videoId comments and the total videoID comments with the comment table. I have a table video and a table comments. …
How to make this Query without using UNION?
I am not very good at making query’s. So could someone explain and help me with this query? I need to inner join 2 tables ‘Slaap’ And ‘Eten’. i need all ‘eten’ where ID = 5 and i need all ‘Slaap’ where ID = 5. I could only achieve this with a UNION but tha…
MySQL trim or get specific set of string using substring locate
I have a column named cea_no from my table. But I want only specific string from it. I already tried several methods but it it wont work for me. Do you have any idea how could I achieve this? Here is my query The result I only want the R017722B part. Removing the “CEA: , the second set of strings
List only repeating names
| personid | first | last | section | | 1 | Jon | A | y3 | | 2 | Bob | Z | t6 | | 3 | Pat | G | h4 | | 4 | Ron | Z | u3 | | 5 …
Running total over duplicate column values and no other columns
I want to do running total but there is no unique column or id column to be used in over clause. there are only 2 columns, name which has duplicate values and the no on which I want to do running total in SQL Server 2017 . expected result: Any help? Answer The following query would generate the output you
Easy substraction of year’s values
I do have the following database table containing the timestamps in unix format as well as the total yield (summing up) of my solar panels every 5 mins: Now I would like to calculate how much energy was produced each year. I thought of reading the first and last timestamp using UNION of each year: This works …
Counter-intuitive behavior of SUM( ) of UNION in Sqlite3
I ran this code in an sqlite3 terminal (version 3.29.0), and the result is a little strange: sqlite> select sum((select 4 union all select 2 )); 4 sqlite> select sum((select 4 union select 2 ));…
Sqlite, using grouping? for subqueries with date ranges
I have a sqlite query that works as-is and it’s used to get an average of date between a date range Again, this works perfectly but I’d like to do some kind of subquery operation that does this same thing for every month and returns the result for each as a different alias like this but my issue i…
SQLite3, Combine multiple rows in one
I have two tables cities (as C) and routes (as R) C: ____ ______ | id | city | |____|______| | 1 | A | | 2 | B | |____|______| R: ____ ______ ____ __________ | id | from | to | distance |…