I have a tables as below I have to update var_start_date column with min(reporting_date) for each combination of id,code,sub_code and item_nbr only till variance field is zero. Row with variance = 0 should have null var_start_date. and next row after that should have next min(var_start_date.). FYI, variance i…
Tag: aggregate
Stored procedure to select values in table1, Group By, Sum, then insert those values into table2
I need a stored procedure to select rows from one table, do a Group By, Sum, and then insert the results into another table with their values. I guess I’ll have to do a merge when …
Postgres GROUP BY, then sort
I have a database query like: SELECT Foo, Foo2, some_calc as Bar, some_other_calc as Bar2, From FooBar — some inner joins for the calcs GROUP BY FOO ORDER BY Bar DESC, Bar2 DESC; I want …
How do I make aggregate query return empty set instead of NULL row?
I have a SQL query like this: However, when t2 selection is empty, it returns a row containing NULL values (because of MAX function returning NULL when called on an empty set). I would like it to return an empty set instead. How can I achieve it? Answer Try this in sql server … try this in Mysql
PostgreSQL, SELECT CASE COALESCE
I need to ‘name’ categories: mycat is a text column with possible values ‘0’ to ‘4’. That works OK, but I have some an error in my program which very rarely writes null (or ” as I can see in pgAdmin). In such cases I have to treat that ” the same as ‘0R…
PostgreSQL – order by an array
I have 2 tables – course that contains id and name of the courses and tagCourse that contains tags for each course. I’d like to write a function that searches courses by given array of tags and returns them ordered by quantity of matching tags. However I don’t know how to write it correctly …
TeraData aggregate function
When I try to select couple of columns with count, I get the following error: Selected non-aggregate values must be part of the associated group My query is something like this. Answer If you’re after a count for each combination of COLUMN1 and COLUMN2: If you’re after a count of all records in th…
MS SQL query to list count by status
I want to achieved the result below. I want to list all records having a status of “For Approval” together with this condition: For example for Username ‘Leo’ Then sum all the status having “For Approval” which is the checker is ‘Leo’ with the condition above An…
Can I use non-aggregate columns with group by?
You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query. I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table …
How to use GROUP BY to concatenate strings in MySQL?
Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C Answer https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL v…