0

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?

3
  • Are there are other restrictions? Is DISTINCT allowed? May 18, 2018 at 9:25
  • No! I just want to use different queries that shows that are possible to have tge same results of GROUP BY, using a different approach.. And the same with HAVING May 19, 2018 at 19:24
  • I'm not sure that it is possible but ok. May 19, 2018 at 20:10

3 Answers 3

2

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
0

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...

9
  • Not in one query, I need in different queries. You can use different approaches for each. May 18, 2018 at 8:11
  • Another question, is your code in pure SQL? May 18, 2018 at 8:45
  • @HenriqueBarcelos "pure SQL" What do You mean? T-SQL? SQL-2011 std? etc.? Regardless I don't know, theory is not interesting for me... sorry.
    – Akina
    May 18, 2018 at 8:58
  • @HenriqueBarcelos the code in the answer is standard SQL. It uses basic SQL (SELECT, FROM, IN, CASE, SUM, MAX) May 18, 2018 at 9:05
  • @Akina - use a CTE or subquery. Add a WITH clause on the outside to simulate a HAVING clause. May 18, 2018 at 10:21
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.