CODEWITHSUNDEEP
sqlwhere-clauseaggregates
user1904766
user1904766

Reputation: 49

Multiple aggregate functions in query

I need to have two aggregate functions in my query, but can't figure out how to filter.

I need the number of samples and the number of samples greater than 1.

something like:

SELECT COUNT(Samples), COUNT(Samples >1)
FROM SampleData

I could do a subquery, but is there a better way to filter like this?

Upvotes: 1

Views: 1397

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221370

In case you're using PostgreSQL, you can use the standard SQL FILTER clause:

SELECT COUNT(*), COUNT(*) FILTER (WHERE Samples > 1)
FROM SampleData

Upvotes: 0

user359040
user359040

Reputation:

To get the number of records, see JW's answer. Similarly, to get the total value of samples, and the total value of samples where samples>1, use:

SELECT SUM(Samples) TotalSamples, 
       SUM(CASE WHEN Samples > 1 THEN Samples ELSE 0 END) SamplesGT1
FROM   SampleData

Upvotes: 0

John Woo
John Woo

Reputation: 263933

You can basically then the value of Sample using CASE and the result of it is the aggregated via SUM().

SELECT COUNT(Samples), 
       SUM(CASE WHEN Samples > 1 THEN 1 ELSE 0 END) 
FROM   SampleData

This will work on most RDBMS though.

Upvotes: 2

Related Questions