Aggregate functions are a subset of SQL functions that compute a single value from multiple input rows, mostly used in `SELECT` queries with a `GROUP BY` clause. Practically all modern RDBMS feature aggregate functions. Typical examples include `COUNT()`, `SUM()`, `MIN()`, `MAX()`, and `AVG()`. ...
2
votes
1answer
27 views
Cannot reference alias of aggregate function in where clause of subquery
I'm having problems with this select statement:
SELECT
field1,
MIN(field2) AS MinOfField2,
(SELECT id FROM table2 WHERE something = MinOfField2) AS table2_id
FROM table1
GROUP BY field1
When ...
2
votes
1answer
23 views
Query in relational algebra without using aggregate functions
Task from exam for subject Database Systems:
I have following schema:
Excavator(EID, Type) - EID is a key
Company(Name, HQLocation) - Name is a key
Work(Name, EID, Site, Date) - All collumns ...
0
votes
1answer
21 views
Create aggregate function not working in hsqldb
I am trying to create a java language routine for an aggregate function in hsqldb following the syntax given in this page - ...
0
votes
1answer
14 views
min with over, without group by: returns unexpected results
I have the following query:
SELECT ContractNr
, SequenceNr
, DurationBeginDate
, MIN(DurationBeginDate) OVER(PARTITION BY contractnr ORDER BY SEQUENCENR ASC) ...
5
votes
1answer
39 views
Oracle: insert-only materialized view
In Oracle, what is an insert-only materialized view?
I have the following materialized view, which uses a MAX aggregate function:
CREATE MATERIALIZED VIEW VM_FAST_MAX
REFRESH FAST ON COMMIT
AS
...
0
votes
2answers
25 views
Error in sql query when using contains in aggregate expression
Query:
SELECT
c,
COUNT
( WHEN a='11' AND contains(b,'aa') THEN 1 ELSE NULL END
) as total
from x
group by c
Error:
Full-text predicates cannot appear ...
2
votes
2answers
46 views
SELECT records that have top n counts for one column
I am using postgresql 9.2.
I have a dataset like this:
ID A B
1 x x
2 x x
2 x x
2 x x
3 x x
4 x x
4 x x
I want to display records with ID that has the top n ...
6
votes
2answers
84 views
Why is PostgreSQL array access so much faster in C than in PL/pgSQL?
I have a table schema which includes an int array column, and a custom aggregate function which sums the array contents. In other words, given the following:
CREATE TABLE foo (stuff INT[]);
INSERT ...
0
votes
1answer
20 views
How to count the number of documents on date field in MongoDB
Scenario: Consider, I have the following collection in the MongoDB:
{
"_id" : "CustomeID_3723",
"IsActive" : "Y",
"CreatedDateTime" : "2013-06-06T14:35:00Z"
}
Now I want to know the ...
0
votes
1answer
12 views
In MySQL, how can I get an aggregate function to return 0 after a where clause and a group by?
I have a table of users and a table of time-entries. I am trying to obtain the sum(time_entry.hours_worked) per employee where the date is within a range of values.
With:
SELECT employee.id, ...
0
votes
1answer
31 views
MSSQL Exclude primary key in group by
I have a query that looks like:
SELECT col1, col2, col3, col4, COUNT(*) OVER(PARTITION BY PAT07, PAT08, PAT18) AS TotalPerPerson
FROM Table1
GROUP BY col1, col2, col3, col4
It returns a table that ...
2
votes
1answer
37 views
Count number of records returned by each group by
I have a general query such as:
SELECT col1, col2, col3, col4
FROM Table1 AS t1
GROUP BY col1, col2, col3, col4
It will return a table something similar to:
col1 col2 col3 col4
data1 ...
1
vote
1answer
48 views
TSQL GROUP BY without aggregating
Consider the following two tables:
key_names
----------------------------------------------
id | name
----------------------------------------------
1 Outside Temperature
2 Inside Temperature
3 ...
2
votes
1answer
40 views
Join across multiple tables with partial counts
Each company, has products and each product has entries in detail1, detail2, detail3 table.
**Table Company**
cid | cname
-----+-----------
100 | Company 1
101 | Company 2
**Table Product**
pid ...
0
votes
1answer
58 views
SQL Server 2012 Aggregate on Split Date Time?
I have data that looks like the below. The first table has a column for date and two columns to represent a one-hour window of time. The second table has datetime and information that needs to be ...
