Questions tagged [conditional-aggregation]
86 questions
1
vote
1 answer
Compute data between First and Last Day Of Last Month
I want to compute the certain column with fix range but I can't figure out the right way.
DECLARE @FirstDayOfLastMonth DATETIME = dateadd(month, - 1, format(getutcdate(), 'yyyy-MM-01'))
DECLARE @LastDayOfLastMonth DATETIME =…
Æthenwulf
- 213
- 2
- 19
1
vote
5 answers
Select final state of a row with versioning
I have a table like this
ID Value1 Value2 value3 Versioning
1 sport tennis 2 1
1 NULL NULL 4 2
1 NULL football NULL 3
1 game NULL NULL 4
This is actually a Custom replicated table from…
PoNti3
- 41
- 1
- 1
- 7
1
vote
1 answer
Running multiple sql queries in hive/impala for testing pass or fail
I am running 100 queries (test cases) to check for data quality in hive/impala. The majority of the queries check for null values based on some conditions. I am using conditional aggregation to count the trivial test cases like below. I want to add…
Defcon
- 807
- 3
- 15
- 36
1
vote
2 answers
How to have count text like in SQL?
How to have count text like in SQL ?
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
Create table #t (message nvarchar(4000), messagedate datetime)
insert into #t values ('Column Listing have data error', getdate())
WAITFOR DELAY…
goofyui
- 3,362
- 20
- 72
- 128
1
vote
2 answers
How to sum up unique values using case statements having certain conditions
I have a table that may have the same item but with different sizes, I would like to count those items with more than one size (e.g. marvel shirt with S, M sizes will count as "1"), but still be able to count how many S and M. I have 2 outcomes I…
BFF
- 378
- 6
- 15
1
vote
1 answer
SQL Pivot data into one row per item
I have a table like this, with product name specified multiple times for each LAY it has:
NAME | LAYER | TYPE | DEPTH
-------------------------------------
32_42_1 | LAY_1 | A | 99.4
32_42_1 | LAY_2 | D | 427.2
32_42_1 |…
Moffen
- 1,817
- 1
- 14
- 34
1
vote
1 answer
Oracle query with group
I have a scenario where I need to fetch all the records within an ID for the same source. Given below is my input set of records
ID SOURCE CURR_FLAG TYPE
1 IBM Y P
1 IBM Y OF
1 IBM Y …
GIN
- 111
- 1
- 10
1
vote
2 answers
how to optimize my oracle sql?
I need count in range two date，this sql is work，bug not better,can you help me?
select dmc.doctor_id,
(
select count(*)
from hele_dct_member_config dmc
WHERE (EXTRACT(YEAR FROM dmc.start_time) = 2016 OR EXTRACT(YEAR FROM dmc.end_time) =…
kailong chai
- 13
- 2
1
vote
1 answer
How to use count,'like' and group by in single query?
I need something like
SELECT sum(case when name LIKE 'nick-1%' then 1 end) as Count,
name
FROM dummytable
GROUP BY CASE
WHEN name LIKE 'nick-1%' THEN 'nick'
WHEN name LIKE 'vicky-1%' THEN 'vicky'
ELSE…
Nick
- 51
- 1
- 8
1
vote
1 answer
Oracle SQL exclude specific type multiple rows select with exact two rows
I am trying to write oracle sql to select all emplids from table ABC
excluding the emplids with three specific roles. example is as follows -
TABLE1= ABC
EMPLID ROLE
______________________
111
Apple
111 …
D Garcia
- 11
- 4
1
vote
2 answers
Multi-column Conditional Aggregation
In SQL Server 2008.
I have things that have components in one of two states, and the table looks like this:
create table Things (
ThingName varchar(10),
ItemNumber INT,
ItemStatus varchar(10));
INSERT INTO Things (
ThingName,
…
Kirk Fleming
- 497
- 5
- 15
0
votes
3 answers
group by in queries that contains subqueries
How can I group by the following query by customerId
select
(SELECT SUM(Purchase) FROM Customers where type in (17,21)) as 'Purchase'
,(SELECT SUM(Point) FROM Customers) as 'Point'
FROM CUSTOMERS
GROUP BY Customers.CustomerID
Dot NET Lover
- 27
- 5
0
votes
1 answer
Is there a way to convert a query contianing multiple subqueries fetching the same column from same table, into one query
Is there a way to convert the following into a query without subqueries (or with a lesser number of subqueries) in order to make it faster.
select ( +(select sum(coalesce(quantity,0)) from transaction where
buyer_number = 101 and…
Jimski
- 826
- 8
- 23
0
votes
1 answer
Concatenate multiple strings ordered (PIVOT)
I'm trying to concatenate the names of multiple Employees (ordered) based on their hierarchical level, which ranges between 0 and 4, without duplicates. The issue itself is fairly easy to solve (although I used Group By when a PIVOT would most…
GTAMN
- 11
- 3
0
votes
2 answers
SQL: how to get sum of grouped items for a given corhort
I have the following orders table:
(1, 2, '2021-03-05', 15, 'books'),
(1, 13, '2022-03-07', 3, 'music'),
(1, 14, '2022-06-15', 900, 'travel'),
(1, 11, '2021-11-17', 25, 'books'),
(1, 16, '2022-08-03', 32, 'books'),
(2, 4, '2021-04-12', 4,…
zeroes_ones
- 171
- 7