I’m super rusty with databases and haven’t got the chance to work too much with them in the past 6-8 years, so there are some things that are uncertain for me. Let’s assume I have a simple bi-directional one-to-many situation: and and I want to perform a query which will return all the parents together with the matching children that
Tag: sql
UNION Results from same SQL each with Different Parameter values
I have SQL that pulls data for a given timeframe. I want to run the SQL multiple times and merge/union the results together into one result set. Currently I copy the SQL and change the date ranges. As an example: Since I need to repeat this for multiple years, I would like to make the year a parameter and execute
BigQuery count each item in array across table
I cannot quite find what I’m looking for, so here goes: I’m looking for a way to get a count of the number of times an item occurs in an array across the entire table. Imagine you have a table child_names with two columns – user_id and children I know it’s unusual to have two children with same name, but
How to reference variables in Snowflake SQL script
I’m writing a script to create 5 tables, but cannot seem to get the SQL DDL part of the statement to work. I get a syntax error ” unexpected ‘table’ “, and I’ve tried the SQL statement with execute immediate as well. What am I doing wrong? Answer To quote the documentation: If you are using the variable as the
How to join two tables based on a calculated field?
I have two SQL queries that output the same kind of output and have the same grouping and order : I would like to join those two results based on the calculated date field (which is the month), and have a result with 3 columns : date, count_users and count_payment_logs. How can I achieve that? Thanks. Answer Something like this
Match specific or default value on multiple columns
raw_data : name account_id type element_id cost First 1 type1 element1 0.1 Second 2 type2 element2 0.2 First 11 type2 element11 0.11 components: name account_id (default = -1) type (default = null) element_id (default = null) cost First -1 null null 0.1 Second 2 type2 null 0.2 First 11 type2 element11 0.11 I seek to check whether the cost logged
Counting orders by supplier results in SQL error
I am trying to get how many orders each supplier have, by showing two columns, CompanyName and number of orders. And then Sorting them by descending order. My SQL: But I get the following errors: Msg 145, Level 15, State 1, Line 65 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Msg 8120, Level
SQL Selecting & Counting From Another Table
I have this query that works excellently and gives me the results I want, however, does anybody know how I can remove any rows that have 0 orders? I am sure it is something simple, I just can’t get my head around it. In other words, should it only show the top 2 rows? ID Company Org Total Orders 1232
Error “more than one row returned by a subquery used as an expression”
I have the following 3 tables: airport area booking And I have this query Running the query I get the error: I don’t understand what the problem is. I added an SQL Fiddle to play with: http://sqlfiddle.com/#!17/8a09f/1 Answer A subquery in the SELECT list is only allowed to return a single value. Not multiple rows, nor multiple columns. If you
SQL double many-to-many relations with only two tables
Having two tables, one of them is called Tasks and the other one Relations where every task in Tasks might have 0-to-many predecessors and similarly, 0-to-many successors that relations among tasks are stored in the Relations-table. So, if Task_1 is predecessor of Task_2 and Task_2 is predecessor of Task_3 then I would like to store the following records in the