5,917 questions
0
votes
1
answer
82
views
Calculate percent of total per firm and filter firms above 50%
I'm trying to calculate the percentage contribution of each firm to the total amount across all firms.
Database:
google-bigquery
What I need (desired output):
Sum usde_haircut_amt per firm
Compute ...
3
votes
3
answers
147
views
How to retrieve a sub-array from result of array_agg?
I have a SQL table in postgres 14 that looks something like this:
f_key
data1
data2
fit
1
{'a1', 'a2'}
null
3
1
{'b1', 'b2'}
{'b3'}
2
2
{'c1', 'c2'}
null
3
Note that data1 and data2 are arrays.
I need ...
- 303
2
votes
2
answers
140
views
Example of partial aggregation
As Postgresql documentation states in # 36.12.4. Partial Aggregation aggregate functions can support partial aggregation.
To do this with custom aggregate function as again the documentation says we ...
- 59
0
votes
1
answer
112
views
Aggregate function for bit-wise OR [duplicate]
In SQL Server 2022, is there an aggregate function for doing a bit-wise OR against a particular column?
-- Example table, data and output to show the different flags
DECLARE @TEST TABLE (FLAGS TINYINT)...
- 20.1k
0
votes
0
answers
102
views
Return multiple columns with aggregate functions [duplicate]
I am trying to figure out how to return multiple columns that correspond with the desired aggregate functions, max of a sum, in SQL.
Based on data from CDC's Serotypes of concern: Illnesses and ...
- 9
2
votes
2
answers
111
views
postgres jsonb array aggregate functions
I have a table with data on user applications. It contains several fields: employee_id text, date date, app_info jsonb.
+-------------+------------+------------+
| employee_id | date | app_info ...
-1
votes
2
answers
192
views
Calculate SUM over a primary key and between dates
My query:
SELECT
c.CustID,
o.OrderID,
SUM(ol.Qty * ol.Price) AS SUMOrder,
AVG(SUM(ol.Qty * ol.Price)) OVER (PARTITION BY c.CustID) AS AVGAllOrders,
COUNT(*) AS Countorders,
SUM(...
- 41
1
vote
0
answers
112
views
A User Defined Aggregated Function fails to shape a Map<K, C> field, where C is an object coming from many rows
Edit: Progressing into this problem analysis, the management of the dataset nested values with tools that Spark offers since 3.1.x seems required.
→ It's my manner of filling nested objects hold by ...
- 3,581
4
votes
5
answers
193
views
Query to find the product name which has a continuous increase in sales every year
Product table with columns - PRODUCT_ID PRODUCT_NAME
PRODUCT_ID
PRODUCT_NAME
100
NOKIA
200
IPHONE
300
SAMSUNG
400
OPPO
Sales table with columns - SALE_ID PRODUCT_ID YEAR QUANTITY Price
SALE_ID
...
1
vote
1
answer
52
views
How to write a query with aggregates of json_array_elements in jOOQ
I have this query:
SELECT organisation_id,
count((j->>'invoiceId')) AS count,
sum((j->>'advanceAmount')::numeric(20,4)) AS sum
FROM runs
LEFT JOIN jsonb_array_elements(...
- 1,791
0
votes
0
answers
94
views
PySpark with aggregations failing
I have a 106M dataframe with nested columns, that is, I have a few columns where the values are {[1,2,3,4,5], <timestamp>, 1, 2 ,3}. I'm trying to add a few more columns using when, then do an ...
- 15.2k
1
vote
1
answer
121
views
Extended condition of aggregating functions SQL
I need group speed by ram and return MAX(speed), where at least one NULL in speed values will return NULL.
ram
speed
128
600
128
600
128
750
128
750
128
800
128
900
32
400
32
NULL
32
500
64
450
64
500
...
- 49
0
votes
2
answers
64
views
SQL row-wise comparison within group
I am trying to compute the number of events that take place within 30 minutes of one another (variable n_within_30) on the same date and in the same location.
I have tried to partition and group by ...
0
votes
1
answer
84
views
BigQuery aggregations on different level by grouping sets
I need to create an aggregation for each housing type (column type) for each destination.
There is a hierarchy (product->sub_product) and (category->sub_category) and also product can have ...
- 19
0
votes
0
answers
52
views
How to get count of unique users [duplicate]
Have the following tables:
Projects:
id
name
1
Project 1
2
Project 2
3
Project 3
Assignments:
id
project_id
user_id
1
1
1
2
1
3
3
1
2
4
2
1
5
3
1
6
3
2
Users:
id
name
1
User 1
2
User 2
3
User 3
I'm ...
- 395
0
votes
0
answers
16
views
Finding the minimum date from a set of dates [duplicate]
I'm having an issue with my assignment where it's giving me more than one "minimum" date.
The problem is to create a table variable that shows the earliest invoice sent by each vendor, ...
- 31
-1
votes
2
answers
148
views
How to sum up numbers in JSONB key-values in PostgreSQL (CockroachDB)?
I have a table with rows with a JSONB in this form (YAML here for readability):
{ foo: 500, bar: 12 }
{ foo: 500, bar: 18, zoo: 5 }
...
There can be arbitrary keys in the objects, but always ...
- 47.2k
-1
votes
1
answer
115
views
How can I apply a filter to a window function in Snowflake?
Suppose I have a table like this
TRANSACTION_DATE
BOOKED_DATE
AMOUNT
2024-02-10
2024-02-09
50
2024-02-10
2024-02-10
50
2024-02-10
2024-02-11
50
2024-02-11
2024-02-10
50
2024-02-11
2024-02-11
50
2024-...
- 144k
0
votes
1
answer
76
views
Find by array of Ids not working with Mongoose aggregate method
I would like to use the aggregate in mongoose to get documents that match the ids in the array.
The array contains mongoose object ids.
It is returning an empty array. Here is my code:
let ...
- 21
1
vote
1
answer
67
views
How to calculate total salary for managers, including their own salary, using a single SQL query?
I have an Employees table with the following structure:
ID
Name
ManagerID
Salary
1
Alice
NULL
90000
2
Bob
1
80000
3
Charlie
1
85000
4
David
2
75000
5
Eve
2
70000
6
Frank
3
72000
I need a single SQL ...
- 11
0
votes
1
answer
61
views
Aggregating IDs within a PARTITION BY query
I've got this SELECT :
SELECT OS.returnmessageid,
OS.tagconfig,
CASE
WHEN OP.messagetype = 202
AND OS.datapointid IN ( 1, 3 ) THEN
CONVERT(NVARCHAR(max), ...
- 61.5k
2
votes
2
answers
491
views
PostgreSQL ERROR: cannot accumulate arrays of different dimensionality
I have a Postgres query I'm trying to aggregate an array of arrays with different dimensions, a simplified form of the query can be written as:
SELECT n, ARRAY(SELECT UNNEST(a) FROM (SELECT ARRAY_AGG(...
- 610
0
votes
1
answer
100
views
Postgres aggregate is slower than just fetching and aggregating on python min
I have 2 queries
explain analyze
select * from reviews
where
product_id = 4922
and time > now() - interval '2 month'
and
explain analyze
SELECT
max(reviews)
FROM
reviews
WHERE
...
- 419
0
votes
1
answer
70
views
SQL aggregate whether table contains a category or type of record
Can anyone think of a better way to structure a SQL query for this problem?
I want to group by account_id and determine whether a type of row exists in the table and just aggregate that to a simple ...
- 1,447
2
votes
2
answers
118
views
WHERE on INNER JOIN table but include all rows that match on right table
I have 2 tables with a 1-to-many relationship. Table A contains 'OBJECTS' and table B contains 'COMPONENTS' have id's that are related and I wrote a query to gather all OBJECTS from table A and ...
- 23
0
votes
1
answer
41
views
How to use condition in column in group by with agg request
it's a bit hard to explain, i dont know how to use a condition in a column-select attribute in a SQL who have group by and aggregat functions...
Session
DateStart
DateEnd
TradeID
TradeKP
1
2024-12-09 ...
- 27
0
votes
4
answers
96
views
Making the SUM clause specific to a given field
I'm trying to get a ratio of 'Good' values to the Total for a given ID. The query below works when I only use one ID for the IN clause. However when I add more than one ID, the Total ends up being ...
- 463
0
votes
1
answer
31
views
Getting the sum of Counts in the same query [duplicate]
I am trying to get the sum of all the counts of this field.
SELECT FieldName,
COUNT(*) AS count,
SUM(COUNT(*)) AS sum
FROM TABLE
GROUP BY FieldName
But when I add the SUM, I get this ...
- 463
2
votes
1
answer
158
views
How to Retrieve First and Last Login Timestamps Per User Without Using Window Functions
I’m trying to get the first and last login timestamps for each user from a logins table that records a new timestamp each time a user logs in. Here’s the structure of my table:
user_id
login_timestamp
...
- 72
0
votes
0
answers
55
views
MongoDB lookup issues
I was trying to use the aggregate function of the MongoDB
Post.findSingleById = function(id) {
return new Promise(async (resolve, reject) => {
if (typeof(id) !== 'string' || !ObjectId....
1
vote
1
answer
73
views
In Postgres, does percentile_disc reuse calculations from other percentile_disc in the same query?
Lets say you have some table you're querying percentiles from, with the same grouping used for both columns:
SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY my_col) as p25,
percentile_disc(...
- 968
1
vote
0
answers
52
views
Confusion Encountered When Using arrayJoin Function and GROUP BY Clause in ClickHouse
the first sql
SELECT rtx
, arrayJoin(['Istanbul', 'Berlin', 'Bobruisk']) AS city
FROM (
SELECT 'hi' as rtx
)
where city = 'Istanbul'
group by rtx, city;
the result is
rtx
city
...
- 21
1
vote
3
answers
103
views
Oracle SQL hierarchical query data accumulation
I need to accumulate data in hierarchical query like this:
select
id,
prior id as parent_id,
count * prior count --need to be accumulated through whole hierarchy like sys_connect_by_path
from ...
- 33
2
votes
2
answers
108
views
Find modal values in each row, with custom tie breaking in PostreSQL
I have table like
create table vals (k, v, z, y, m) AS VALUES
(0, 0, 1, 8, 0),
(1, 2, 1, 1, 6),
(1, 0, 0, 2, 2),
(5, 4, 6, 8, 9),
(0, 0, 0, 6, 6);
I want to calculate modal value for each row. ...
-1
votes
1
answer
61
views
How to select the Average of a Sum column in the same result
I have a table of operations with dates and quantities.
dateTime
quantity
2024-01-01
1
2024-02-5
2
I want to sum the quantities for each month and then group by each month, which is working fine.
Then ...
- 13
0
votes
1
answer
126
views
MySQL full-text search with aggregate functions
I have a website connected to a MySQL database of songs, and am working to improve full-text search on the site. A simple query with MATCH() works as expected, but as soon as I add an aggregate ...
- 296
0
votes
2
answers
305
views
Implementing range between dates in a window
I am migrating some legacy code from an Oracle DB to an SQL Server DB. I have come along this piece of code, with the purpose of calculating the cumulative amount over the last 6 months/ year, for ...
- 11
1
vote
1
answer
102
views
Complex point aggregation with modifiers
I have the following tables in a Postgres 16.5 database, tracking player completions for levels, with some modifiers:
CREATE TABLE levels (
id int PRIMARY KEY
, points int NOT NULL
);
CREATE TABLE ...
- 113
0
votes
1
answer
125
views
Max() and Min() have different behaviours?
Why is the behaviour of min() different from max()?
Select
max(My_Date) over (order by My_Date) as Max_Datum
, min(My_Date) over (order by My_Date) as Min_Datum
From
(
Select dateadd(m,-1,...
- 458
1
vote
1
answer
57
views
aggregating MAX() over a group over the MAX() of a subgroup
I have a table of which looks something like:
sport
date
daily_attempt
soccer
11/12/2021
1
soccer
04/09/2023
1
soccer
04/09/2023
2
swimming
07/07/2022
1
swimming
08/08/2024
1
swimming
08/08/2024
2
...
- 167
3
votes
1
answer
134
views
Why the order by in XMLAGG doesn't give the same result as the same order by on a SELECT?
Why does the XMLAGG give me a different order than my query even though they have the same ORDER BY?
I can't figure out how to get my xml in the same order as my query. I tried using CONVERT(file_name,...
- 1,784
2
votes
1
answer
41
views
Why does one aggregate query work and the other raise an error?
I just found something very strange in the Postgres test suite.
-- test for outer-level aggregates
-- this should work
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 ...
- 85k
0
votes
0
answers
482
views
Facing FieldPath field names may not contain '.'. Consider using $getField or $setField. when using $cond operation in mongo
I am trying to create a query where I am filtering out the number of events each day, which is represented by totalCount. Now after that, I am again filtering the number of events based on some ...
-1
votes
1
answer
41
views
How to aggregate dataframe to keep certain column values in separate cells?
i am using python. i have a dataframe:
# Sample DataFrame
data = {
'sto_num': [1, 1, 1, 2, 2, 3, 3],
'sto_text': ['ab', 'ab', 'ab', 'k', 'k', 'b', 'b'],
'tu_num': [2.2, 2.6, 3.5, 1.2, 1.3, ...
- 29
0
votes
1
answer
170
views
Tableau Sum is already an aggregation and cannot further aggregate
I’m trying to take the sum of the count of a column and it’s telling me sum is already an aggregate and can not further aggregate.
Attempt: Sum(Count of Cases)
The count of cases is a calculated file ...
- 33
2
votes
1
answer
120
views
MySQL ask to add column to GROUP BY which I don't want to use in GROUP BY
I have a query that should create a view that will be used to be exported as csv file. Here's the query:
CREATE OR REPLACE VIEW coupons_export AS
SELECT
coupons.code AS coupon,
...
0
votes
1
answer
80
views
Create sets within groups using SQL in PostgreSQL
I am trying to create sets from existing rows but having difficulty. I have some success but it is not coming to what I am expecting.
Here is my existing data and my desired outcome. I tried many ...
0
votes
1
answer
403
views
Need to get the result in array(row(tid array(varchar), res varchar)) format using Trino SQL
I have a table - NS_Data
ns
tid
res
nid
PQR
ITKT20254
not include
A
PQR
ITKT20223
not include
A
PQR
ABCD23456
not include
B
PQR
ABCD54321
not include
B
PQR
ITKT21111
include
A
I want the output in ...
- 45
1
vote
1
answer
299
views
Need to format the data in trino sql
I have the table FruitMarket
Fruit
Color
City
apple
red
Shimala
apple
Green
Shimal
apple
blue
Shimala
apple
yellow
Manali
grape
black
Nagpur
grape
orange
Nashik
grape
purple
Nashik
grape
white
Nagpur
...
- 45
5
votes
1
answer
249
views
Aggregated rollup in PostgreSQL with avg and sum in different levels?
I have a PostgreSQL query that aggregates data from multiple tables and generates a report. Currently it works perfectly fine and it is quite simple. Here is the query I'm using:
SELECT
a.name AS ...
- 7,064