I have two tables stores_data and financial_week as shown below. Stores data is a summarised data across multiple attributes. My task is to generate data for all the weeks present in the second table, if data is missing, the quantity should be listed as 0. Expected Result set is this – I have done cross…
Tag: sql
How to use the results of a query to filter a table comparing one field to the result?
I want to filter a table showing only the rows where total is between ± 3 standard deviations from the average. The query I’m using is this: But I’m getting the error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the …
Oracle trigger multiple conditions in when clause
I’m trying to create a trigger that updates a column in a table when other columns are updated. but getting the following error while saving the trigger ORA-25000: invalid use of bind variable in trigger WHEN clause My trigger is as follows, I’m not sure what is wrong with the code. Answer Althoug…
Hive Explode the Array of Struct key: value:
This is the below Hive Table And this is the data in the above table- Is there any way I can get the below output using HiveQL? I tried use explode() but I get result like that: Answer Use laterral view [outer] inline to get struct elements already etracted and use conditional aggregation to get values corres…
Weird behavior of MySQL Server when ordering by 2 conditions
Say a database table my_table has the following structure: and some of the rows in the database have total_votes == 0. I want to get the rows ordered like this, except I also want to include the rows that have total_votes == 0. I tried using the following query, and it worked like a charm: My question is why …
Convert list of dictionaries into dictionary in Postgres jsonb
I have an actions_table looking like: I would like to aggregate by profile_id and jsonify as: The closest I could get to is: By means of the query: How do I get to my desired result from here (convert list of dicts into dict), or how do I fix my original query? CAVEATS I need a general answer, I might
Iterate through query
I’m trying to iterate through simple query This is what I managed to create But I get: Answer You don’t need a while loop for this. You can do this in a single query. One method is: Trying to use a while look is troublesome. Such programming constructs would be in programming blocks. They are desi…
Generate dates in-between date and date after
Based on the table below I want to generate the dates in between, by dividing the revenue by the number of days from the date in the current row and the row below. Example: Revenue in US on 1 January 2020 is 120. The next revenue date for the US is 5 January 2020. There are 4 days in betweeen.
SQL SELF JOIN – driving me crazy
Can someone please tell me how to solve this question? And the thought process – like how do you think while solving this. It is driving me crazy. 🙁 Question – The attendance table logs the number of people counted in a crowd each day an event is held. Write a query to return a table showing the d…
How to left join when only matched one record from right table
I want to know How to left join when only matched one record from right table. For example, tableA id value 1 34 2 42 3 60 tableB id value tableA_id 1 20 1 2 31 1 3 50 2 I want to get result like below using left outer join. tableA_id tableA_value tableB_value 1 34 null 2 42 50