I’ve tried the suggestions I’ve found on SO and cannot get the results I’m looking for which is this: I am able to get the first two columns with this code: — What I’ve tried: Trying to use a calcuation in a subquery to represent the total number of rows in the table is beyond me so far, so I
Tag: group-by
SQL GROUP BY with LEFT JOIN MS SQL Server
I have a calendar table (c) with a set of 3 month dates: I have a Stats table (s) with views of each product on the website. I need to count the views per product per month whether there are views or not. I have followed many SO answers (SQL – Group By with Left Join) and I cannot see
How to calculate rows count in where statement in sql?
I have two tables in SQL Server: order (columns: order_id, payment_id) payment (columns: payment_id, is_pay) I want to get all orders with two more properties: How many rows where is_pay is 1: And the count of the rows (without the first filter) So I wrote this query: The problem is how to calculate the rows without the is_pay = 1?
SQL SUM values by DISTINCT column after JOIN
I’ve been searching the forums for a solution for hours now but I couldn’t quite find the one that is fitting for my problem. I have two tables: members **************** id name city 1 John Boston 2 Maria Boston 3 Steve London 4 Oscar London 5 Ben Singapore donations ******************** member_id amount 1 100 1 150 2 300 3 50
How to select other rows in GROUP BY
Sorry for the bad title. I don’t know how to summarize it better. I have a table with unix timestamps and other values, like the following. time value 1620916380 110 1620916440 100 1620916500 120 1620916560 120 1620916660 90 The entries are always one minute apart. I want to group entries into 5-minute blocks with the value averaged. Currently I use
Complicated group by – windows
Can you please help with the following grouping: Name can have states that can be repeated several times and it is essential in output to have all the state changes over time and to have the starting date of the state Here is the input data: Desired output Answer Assuming you want to find the start date per consecutive sequence
SQL query to consider exactly last 3 email send count and then return the respective click count
I have a table – ‘clicks_plus_noClicks_raw’ like below If customer has not clicked on any of the last 3 emails we sent(exact 3), I want to add the result to final table with count as 0. a@gmail.com & b@gmail.com should be skipped because we dint send them 3 emails yet.The final results should be only for ones we sent 3
Find the names of the suppliers who supply all the parts in MS Access
I have three tables: I want to find the names of the suppliers who supply all the parts in MS Access. This code does not work properly: What could be done better? Answer You need a CROSS join of Suppliers and Parts and a LEFT join to SPB. Then you group by supplier and set the condition in the HAVING
Lead window function in mysql to find sales
Given this table. I would like to know for each day how many different customers made a sale on date t and and t+1. The result for date 2021-06-30 is 2 because customer 1 and 3 made a sale in t and t+1. Answer Use LEAD() window function for each distinct combination of date and customer to create a flag
How to fill missing values in aggregate-by-time function
I have function (from this question) which groups values by every 5 minutes and calculate min/avg/max: However, due to missing values, some five-minute periods are skipped, making the timeline inconsistent. How to make it so that in the absence of data for a certain period, the value of max / avg / min becomes 0, instead of being skipped? For