I have a table (simplified example below) consisting of TEST(s), TESTPRICE per test and TESTSTATUS. I would like to be able to run a query that will result in two aggregated columns, one for pending …
Tag: aggregate-functions
SQL aggregate function on result of another aggregate function
Short backstory for this question. I am a student who works part-time as a tutor for several classes, including an entry level SQL class. I’ve got a student who has a fairly complicated database, compared to what most people get out of this class. Since their database ins’t super simple, they’e run into a problem trying to get a number.
Logic to use in place of Aggregate function the WHERE clause
I am computing the frequency of values over a large table. since the result of the aggregate functions result in zero for some rows, I would like to simply skip such rows. I could have used NULLIF(..) but I do not want to return a zero value. And aggregate functions aren’t allowed in the WHERE clause. How do I refactor
How do I create cohorts of users from month of first order, then count information about those orders in SQL?
I’m trying to use SQL to: Create user cohorts by the month of their first order Sum the total of all the order amounts bought by that cohort all-time Output the cohort name (its month), the cohort size (total users who made first purchase in that month), total_revenue (all order revenue from the users in that cohort), and avg_revenue (the
PostgreSQL: aggregate records by time interval
I would like to generate reports of GPS capturing rate by travel modes. I have in a table modes the type of travel modes used by users. So for example, the following is the sample data for travel mode by user 10 for different trips. And then for each user and for each trip, user’s GPS traces are recorded in
Postgres get multiple rows into a single json object
I have a users table with columns like id, name, email, etc. I want to retrieve information of some users in the following format in a single json object: Wherein the users unique id is the key and a json containing his information is its corresponding value. I am able to get this information in two separate rows using json_build_object
Sum multiple columns in multiple rows
I have following data structure for sales: I need to know how many products with id=1 were sold for the last 12 month. If we are in June 2020 I need to sum M01, M02, M03, M04, M05 (WHERE year=2020) and M06, M07, M08, M09, M10, M11, M12 (WHERE year=2019) WHERE id=1. I should get a value of 36. Please,
Aggregate function not accepted inside WHERE clause
I have the following command. With this PostgreSQL schema. After adding AND day_of_week=3 to WHERE I get the following error. Why cannot I restrict this column? Answer You cannot use a derived column in a where clause. A simple solution is a lateral join: Some advice: Do not use NATURAL JOIN. There is nothing “natural” about it, because it does
Athena sql query to find items not containing a value
I have a table in a bucket, I am using Athena to get the required data My table looks like I need to find all the resources where A-1 is not found the result should give me i-2. How to write in sql Answer You can use aggregation to group all rows having the same resourceid together, and then filter
When to Use * in SQL Query Containing JOINs & Aggregations?
Question Web_events table contain id,…, channel,account_id accounts table contain id, …, sales_rep_id sales_reps table contains id, name Given the above tables, write an SQL query to determine the number of times a particular channel was used in the web_events table for each name in sales_reps. Your final table should have three columns – the name of the sales_reps, the channel,