I am trying to get all rows where the states are greater than 1. My table: user_id|state ————– 1000000|Active 1000000|Created 1000001|Active 1000000|Deleted 1000002|Active 1000001|Created …
Tag: group-by
Group By and Count the total number of same status value
I have 2 tables, Order and Store. And here are some of the fields from the table I need. Order table OrderId StoreId SystemOrderStatus 1 1 Received 2 1 Sent …
How to fix ‘must be an aggregate expression or appear in GROUP BY clause’ with ‘as’ clause
I am trying to get the number of request by hour of my CloudFront distribution using athena query. I created cloudfront_logs table guided by this Link in my sample_db Below is the query I made to …
Counting the number of words in a Column in Oracle SQL
How do you group this data, based on Patterns? Is it possible in SQL? CREATE TABLE ABC (“NAMES” VARCHAR2(50 BYTE)) ` `INSERT INTO ABC (names) VALUES (‘CA Apple 3’); INSERT INTO ABC (names) VALUES (…
Using ROLL UP/CUBE in conjunction with PIVOT [Oracle]
I have a table in Oracle that looks like this: year month customer ———————— 2011 Jan Smith 2011 Jan Smith 2012 Feb Howard 2013 Feb Howard … Now I …
SQL OR Linq: How to categorize into different batches when category changes
I have the following data: Timestamp Min MAX Category 2019-03-22 08:10 12.00 5.10 AAAA 2019-03-22 08:11 10.00 5.20 AAAA 2019-03-22 08:12 11.00 4.90 AAAA 2019-…
How to aggregate count of rows having the same value in a sequence?
I have a query that returns data in the following sample: SELECT timestamp, atm_id FROM TRANSACTIONS ORDER BY TIMESTAMP ASC; Output TIMESTAMP | ATM_ID | ——————– 2010-01-01 | EP02 | …
SQL Server : sum and multiplies on 2 tables
I need help to SUM and MULTIPLIES on join 2 tables: tb1 tb2 In tb1, I need to sum QTY and multiples SKU with PRICE without repeating same SKU (21135208, 21035621): Current query: My result is: Expected result is: Answer I believe that you could filter out duplicates by using a subquery like SELECT DISTINCT &#…
Oracle SQL: select count of multiple distinct fields
I need to select id, type and count of rows with unique id-type combinations. So I tried something like this: SELECT ID, CASE WHEN /**/ THEN ‘FIRST’ WHEN /**/ THEN ‘ANOTHER’ …
Query slow when use count(*) and inner join
My query is taking around 5 seconds to run. It returns almost 5000 records. If I remove the Select Count(*) …, it runs very fast, but I need this data. Is there a way to improve this query? Thanks Answer Indexes recommended by Gordon Linoff are a must-have. Another thing you could to is move the computa…