Alternative to Having and Group By

Question

I'm in the DB classes, and I have to understand and show that it is possible to get the results of HAVING and GROUP BY without using themselves. I searched for alternatives for those 2 commands, but I didn't understand anything.

Can someone give me examples?

Answer 1

Assuming a query like:

SELECT category, COUNT(*)
FROM T
GROUP BY category
HAVING COUNT(*) > 3

You can mimic that with:

SELECT category, cnt
FROM (
    SELECT x.category
       , ( SELECT COUNT(*) from T where T.category = x.category ) as cnt
    FROM (
        SELECT distinct category from T -- GROUP BY
    ) as x
) as y
WHERE y.cnt > 3 -- HAVING

Answer 2

Implicit GROUP BY:

SELECT SUM(CASE grp='first' THEN val END) SumOfFirst
     , SUM(CASE grp='second' THEN val END) SumOfSecond
     , MAX(CASE WHEN grp IN ('first','second') THEN val END) MaxInFirstAndSecond
FROM table;

Implicit HAVING? Implicit both GROUP BY and HAVING in one query? I cannot come up...

Answer 3

It is possible if you write a small program for the purpose. For example, suppose you don't want to run this:

SELECT category, COUNT(*) FROM product GROUP BY category;

First, you will need to know which categories exist:

SELECT DISTINCT category FROM product;

Then, for each category, you get the number of products:

SELECT COUNT(*) FROM product WHERE category = 'something';

To emulate a HAVING condition, you can filter out the results in your program. You cannot do that in WHERE.

Of course in the real world you will never have to do something like this.