Questions tagged [conditional-aggregation]
86 questions
22
votes
2 answers
Conditional aggregation performance
Let us have the following data
IF OBJECT_ID('dbo.LogTable', 'U') IS NOT NULL DROP TABLE dbo.LogTable
SELECT TOP 100000 DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0) datesent
INTO [LogTable]
FROM sys.sysobjects
CROSS JOIN…
Radim Bača
- 10,646
- 1
- 19
- 33
5
votes
1 answer
Replicating FILTER clause from Postgres in Snowflake
Postgres have a really useful method called FILTER which doesn't seem to be in Snowflake. In Postgres it works like this:
SELECT
user_id,
MIN(orders.started_at) FILTER (WHERE orders.sequence_in_subscription = 1) as time_of_t1
FROM…
mylescc
- 5,720
- 3
- 17
- 23
5
votes
1 answer
MySQL conditional SUM using GROUP BY and DISTINCT
I have the following data structure in my foos table:
-----------------------------------------------
| id | bar_id | baz_id | date | value |
-----------------------------------------------
| 1 | 1 | 1 | 2013-12-01 | failure |
| 2…
user1454117
2
votes
2 answers
Use multiple INNER JOINS to transpose one column in multiple columns
I have the following table
CREATE TABLE "holes" (
"tournament" INTEGER,
"year" INTEGER,
"course" INTEGER,
"round" INTEGER,
"hole" INTEGER,
"stimp" INTEGER,
);
With the following small sample of data:
33 2016 895…
HJA24
- 410
- 2
- 11
- 33
2
votes
2 answers
GROUP BY and create columns for each unique value grouped on (output as 1 row always), then use the output as a JOIN - SQL Server
I need to GROUP data BY 2 columns (BillingId and PaymentType) - no issues with that AND have output of 1 row with columns of unique PaymentType - issue with this step. So each BillingId has only 1 row and that table will be used for joining another…
Yara1994
- 317
- 2
- 11
2
votes
3 answers
Finding duplicate values in multiple colums in a SQL table and count for chars
referring to this question:
Finding duplicate values in multiple colums in a SQL table and count
I have the following table structure:
id name1 name2 name3 ...
1 Hans Peter Frank
2 Hans Frank Peter
3 Hans Peter Frank
4 Paul Peter Hans
.
…
MikaBA
- 57
- 4
2
votes
1 answer
SELECT MySQL with 02 counts in same table with WHERE and GROUP BY in same Query
I am trying to do 02 COUNTS in same table, using WHERE and GROUP BY, but the result of the last column is coming wrong...
Thats the command SQL which I writed till now:
(SELECT estado_sigla,
estado,
Count(*) AS numero_advogados,
…
Sophie
- 410
- 3
- 10
2
votes
2 answers
Select count of multiple columns WHERE another column is distinct
I need to find out how many people ordered what type of food. My table looks something like this:
PersonId (int)
Hamburger (varchar100)
Fries (varchar100)
Soda (varchar100)
1
"Unique burger"
"Unique Fry"
"Unique Soda"
2
"Unique…
morganherg
- 63
- 9
2
votes
2 answers
I'm querying a table but I need the output in different columns
Let's say I have a table, sample below
Member ID | Hotel ID | Country |
----------- ----------- ----------
100 ZZA Country 1
101 ZZA Country 2
102 ZZB Country 2
103 ZZC Country 3
201…
bixby
- 23
- 3
2
votes
1 answer
Using DISTINCT with Multiple Condtional Aggregations (Annotate)
Python: 2.7
Version: Django 1.11
Hello,
I am having issues with using conditional aggregation with COUNT DISTINCT in my Django query.
The problem is when I join a separate table, my distinct count on the IDs does not seem to work.
I have a query…
Aero Chocolate
- 1,477
- 6
- 23
- 39
2
votes
4 answers
SQL aggregate rows with same id , specific value in secondary column
I'm looking to filter out rows in the database (PostgreSQL) if one of the values in the status column occurs. The idea is to sum the amount column if the unique reference only has a status equals to 1. The query should not SELECT the reference at…
OAK
- 2,994
- 9
- 36
- 49
2
votes
3 answers
SQL Conditional aggregation
I'm using Oracle.
Having a table as:
Year Type Value
2011 1 500
2011 2 550
2011 3 600
...
...
2012 1 600
2012 2 750
2012 3 930
I needed to subtract all the values from different types, grouped by…
valter.vx
- 137
- 4
- 11
1
vote
2 answers
MySQL count percentual of occurrences greater than
I have a table like this.
+ day + person + amount +
+------+---------+--------+
+ 1 + John + 4 +
+------+---------+--------+
+ 1 + Sam + 6 +
+------+---------+--------+
+ 2 + John + 3…
jackomelly
- 523
- 1
- 8
- 15
1
vote
1 answer
Counting records based on conditions over potential null values
I have this tables:
CREATE TABLE customer_orders (
"order_id" INTEGER,
"customer_id" INTEGER,
"pizza_id" INTEGER,
"exclusions" VARCHAR(4),
"extras" VARCHAR(4),
"order_time" TIMESTAMP
);
INSERT INTO customer_orders
("order_id",…
Ale
- 107
- 6
1
vote
2 answers
How to return 0 for all time intervals instead of nothing when counting
I have a query for deployment table. There is no data for hotfix column now. I want to show all change count without hotfix and with hotfix for time intervals.
Table…
Pronto
- 179
- 1
- 2
- 11