5,898 questions
0
votes
1
answer
55
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
49
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 ...
- 351
0
votes
0
answers
15
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
97
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 ...
- 47k
-1
votes
1
answer
68
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-...
- 143k
0
votes
1
answer
71
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
50
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
53
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), ...
- 61k
2
votes
2
answers
227
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
84
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
60
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,427
2
votes
2
answers
104
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
31
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 ...
- 29
0
votes
4
answers
85
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 ...
- 451
0
votes
1
answer
23
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 ...
- 451