I am looking to display the group which is grouped on id column where trans_cd=’Audit’ only on max trans_proc_dt of that group. There should not be any other trans_cd=’Audit’ execpt for max trans_proc_dt. My output should be I can’t think how to go about getting my result set. An…
Tag: group-by
GROUP BY with Key:Value filters for each group
Given a table with following schema: RecordId ProcessId Data 80830F00-379C-11EA-96B7-372060281AFC 1 4.2 80830F00-379C-11EA-96B7-372060281AFC 2 445588662211448 80830F00-379C-11EA-96B7-372060281AFC 7 1 791F6D30-379C-11EA-96B7-372060281AFC 1 3.3 791F6D30-379C-11EA-96B7-372060281AFC 2 999999999999999 791F6D30-379…
How do I return a row with 0 if the group by has 0 records?
I have this alert_levels table: Then I have this alerts table The alert_level_id in the alerts table is a foreign key of id from the alert_levels table. What I want is to count the number of occurences of each alert_type grouped by the alert_level_id whithin a chosen time period. And if there is no occurency …
SQL Count Customers where Products are the same
I have a problem. I have a SQL-database named customers with the 3 columns Country, CustomerID and Product. With that database, I want to count all the customers which bought the products bike, flask and helmet but grouped by the country as you can see in the following picture. Is there a chance you can help …
Group by month and name SQL
I need some help with SQL. I have Table1 with columns Id, Date1 and Date2 Table2 with columns Table1Id and Table2Id Table3 with columns Id and Name Here is my try: I need to take Count(date2)/Count(date1) grouped by monthes and name. I have no idea how to do that, as there is no table with monthes. DB –…
Redshift SQL sumup amount from two groups only when group 1 is found
I have a requirement where I have to add amounts from two groups only if first group exists. So far, i have selected distinct groups and the corresponding ids. But i am not sure how to proceed further….
Query with group by with CustID and amounts daily and MTD
I have the following data: ID username Cost time 1 test1 1 2021-05-22 11:48:36.000 2 test2 2 2021-05-20 12:55:22.000 3 test3 5 2021-05-21 00:00:0-0.000 I would like to count the costs for the username with a daily figure and a month to date figure in once table I have got the following This will return the mo…
“IS NOT NULL” not producing the required results in mySQL
I am trying to build a report based of my table. Here is my table: Following is the SQL query to get desired results. I am looking to exclude entries which have all null values and following is my syntax but it is not working and produces the same results. Kindly help here or share a post that can help.
Divide the column by the sum grouped by another column
I have a table like this and I would like to divide the frequency by the sum of frequency grouped by the source. Therefore, I am looking for a table like Is there any way to do this in a single sql query? Answer You can use SUM() window function. If you want a numerical result: Depending on your database,
Count from multiple columns and rows simultaneously in SQL
I have a table of matches which look like: I need to find the total matches played by every team and the output should be like this: I know about SUM() and GROUP BY clauses but not able to do it on multiple columns. Any help would be highly appreciated. Answer You can use union all to unpivot the data