I am trying to find the employees who have sales figures that are above the average sales totals for a particular company. I am using Microsoft SQL Server, and here is what I have so far: However, my query returns nothing. Is this the correct way to write something like this? What could I do to simplify things? Answer If
Tag: aggregate-functions
Cohort Analysis using SQL (Snowflake)
I am doing a cohort analysis using the table TRANSACTIONS. Below is the table schema, Below is a quick query to see how USER_ID 12345 (an example) goes through the different cohorts based on the date filter provided, The result for this query with the time frame (two weeks) would be and this USER_ID would be classified as a Regular
I tried solving this problem by using MAX function but get the result . Is there a way to solve it by using any other function?
I need to get “second updated_time” for each order and sorting them by “update_time”. I wrote the following query, which is not giving any output. Suppose, we have to calculate get the time difference between second updated time and first updated time against each order_id ,if we sort the table on update_time (same question but extended) Server is Mysql. The
LISTAGG in SQL is returning a row with null values
I have 2 tables A and B, B is having a foreign key relationship with A i.e.,(b.detail_id = a.id) I want to apply LISTAGG query on one of the column in B. The above query is returning me a row with all values as null, But I want no rows. How can I achieve this? If it’s not possible any
MySQL : Calculate percentage composition based on a specific value
I have the following MySQL table named Table1 The Result can either be Pass or Fail. I want to calculate the percentage pass for each id. Result must be the following table based on formulae: For each id, count Pass / Total Counts for given id. For example id 2 have 2 pass and 2 fail therefore percentage pass =
SQL – An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause
If you see the picture below, I only want to see those categories where the average revenue is greater than the overall average. The query that I’m using is below. The very last line is causing the issue. The error that I’m getting is: An aggregate may not appear in the WHERE clause unless it is in a subquery contained
Making cumulative sum with ids and dates in MySQL
Imagine I have this: id transactiondate transactiontag clientid transactionvalue 1 2020-12-28 pizza 0 29.99 2 2020-12-29 payment 0 -39.99 3 2020-12-28 cheese 0 -109.99 4 2020-12-28 cheese 0 -109.99 5 2020-12-28 pizza 1 -19.99 6 2020-12-28 cheese 1 -18.99 7 2020-12-28 salary 1 58.99 8 2020-12-29 salary 1 38.99 So I have all the Ids of transactions, when they were
output need to achieve by one query
I have table which consist data like below. I need output in pivot : I can achieve by separate queries easily but is it possible to get it done by one query? Answer You can use conditional aggregation such as in order to return results pivoted.
mysql 8 pivot query should return a non null value
I would like the following pivot query to show value 0 instead of null, Output for the above is, Employee id 122 does not have a Repayment value so the desired output is, dbfiddle Answer I don’t see the need for the second branch of the repayment case. If you want 0 when the category is not available, just else
Selecting first and last time stamps of a section
I have a MySQL database with a table like: I would like to select from this table the first and last timestamp for each value of each Batch_Number. I would like the table to look like: I am not sure how to select both, when the previous Batch_Num is different from the curent one, and also when the next one