(I’ve created a similar question before, but I messed it up beyond repair. Hopefully, I can express myself better this time.) I have a table containing records that change through time, each row representing a modification in Stage and Amount. I need to group these records by Day and Stage, summing up t…
Tag: sql
Why select invalid field in subquery could run in BigQuery?
For the following sql Since there is no a in temp.a1 and there should be an error here, However, the output of Bigquery is Why the result happened? On the other side, when run select distinct a from temp.a1; there is one error Unrecognized name: a comes up. Answer Your query is: You think this should be: And …
SQL: Optimize query with multiple CASE statements
I have a query with many CASE statements that runs for a very long time due to the number of rows. In my research I have not found a solution yet. Is there a way to write the CASE statements more efficiently and with better performance? database: Oracle table_a table table_y are all the same table where I SEL…
calculation with undefined number of rows
I would like to add a calculation taking into account we could have one, two, three or a lot of more rows. We can get all the data using the relations but I am a bit stuck because the number of relation are undefined. For example, as a source: Source Table id amount debt relation rn 123 250 225 NULL
Splitting up events that occur over the day boundary
I have a table of events with a start time and an end time, with some events that have a start time before midnight and an end time after midnight. I’d like to produce output that splits up these events at the midnight barrier so they can be counted toward their respective date. The above table could be…
Athena/Presto : Unnest 2 arrays with left join
So I have 2 Json arrays that need unnesting, and joining based on a key within the json structure. In theory is easy, but without having a ‘left join unnest’ functionality, it all becomes messy. I have achieved what I want, by grouping the results; but I also have concerns that it is doing 2 cross…
User Defined Column name in select statement in Hivesql
I need to create user defined column name like below Postgresql query into HiveSql. Could you please help me on this. Answer Use backticks: But it is not possible to preserve case due to Hive limitation. Resulted column name will be in lower case: total customers See this answer: https://stackoverflow.com/a/5…
Oracle SQL – need to flip values, but don’t know how
This is the output, but I need those values changed. I have ID primary keys for both – kasutaja and riistvara, but I don’t know how to match kasutaja ID 1 with riistvara ID 2 and vice versa. And the output should be like this: The R_NIMETUS and SEERIA_NR fields are different on my output what I ge…
Database search results with context
I’ve built a system for archiving, aggregating, and indexing my IM history across different platforms (e.g. MSN Messenger, Hangouts, WhatsApp). The data is currently stored in an SQLite3 database, and relevant parts of the schema look something like this: There are also NOT NULL constraints on all colum…
Select everyone with the same ips
There is a table of users users: ETC… It is necessary to select all users with 1 ip. I tried to do it, it doesn’t work, not what I need to choose. Answer For MySQL 5.x use