How to return multiple aggregates from a single query, all with different constraints?

Question

Say I have a table that has the transactions of a store.

How can I count the number of transactions that have the word "crackers" in it, the number of transactions that have "soda" in it, and the number of transactions that have both "crackers" and "soda" in it.

If possible, this should be in a single query.

You could do three aggregate queries and then union them, thus getting one row per sub-query. Or you could use subqueries in the column list, and end up with one row, with each column containing a subquery count (sgeddes has an answer of this kind). — halfer, Feb 26 '16 at 00:18
Which database system are you actually using, Ricky? You have two database tags here. — halfer, Feb 26 '16 at 09:56

score 2 · Answer 1 · answered Feb 26 '16 at 00:18

You can use conditional aggregation for this:

select 
   sum(case when field like '%crackers%' then 1 else 0 end) crackercount,
   sum(case when field like '%soda%' then 1 else 0 end) sodacount,
   sum(case when field like '%soda%' 
             and field like '%crackers%' then 1 else 0 end) bothcount
from yourtable

SQL Fiddle Demo

score 2 · Answer 2 · edited May 23 '17 at 10:30

In Postgres 9.4 or later, use the dedicated aggregate FILTER clause:

SELECT count(*) FILTER (WHERE col ILIKE '%crackers%') AS ct_cracker
     , count(*) FILTER (WHERE col ILIKE '%soda%')     AS ct_soda
     , count(*) FILTER (WHERE col ILIKE ALL ('{%crackers%, %soda%}'::text[]) AS ct_both
FROM   tbl
WHERE  col ILIKE ANY ('{%crackers%, %soda%}'::text[]);

There are a couple of standard techniques that work for older versions or MySQL, too:

For absolute performance, is SUM faster or COUNT?

Assuming you want case-insensitive pattern matching, hence ILIKE.

The ANY and ALL constructs are handy features, especially for longer arrays, but not strictly necessary for your problem. Also, the WHERE clause is not strictly needed, but typically improves performance.

Details:

How can I simplify this game statistics query?

How to return multiple aggregates from a single query, all with different constraints?

2 Answers2