Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Having, a less understood SQL clause

smallthingssql.com

Post reply on HN
Print view View original

21–30 of 83 posts

Re: Having, a less understood SQL clause

#21

Post by rjbwork »

kmac_ wrote: post #17
galkk wrote: post #5

Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this article... "group by cube/coalesce" is much more complicated thing in this article than "having" (that could be explained as where but for group by)

SQL opens up when used with OLAP schemas. Most devs are experienced in querying "object mapped" schemas where cube, roll up, etc. are not useful. Nothing bad per se, but it can give an impression that SQL is a bad language, when actually it clicks well with a proper data schema.

Indeed. I think your mind can really be opened by having to answer complex business questions with an expansive and well designed data warehouse schema. It's a shame it's such a relatively niche and unknown topic, especially in the startup world.

Re: Having, a less understood SQL clause

#22

Post by conceptme »

ramraj07 wrote: post #12
galkk wrote: post #5

Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this article... "group by cube/coalesce" is much more complicated thing in this article than "having" (that could be explained as where but for group by)

The only “standard” feature id rather try not to understand is recursive CTEs lol

it's pretty useful when working with hierarchical data, but you do not to put some check for cyclical relations, I have seen those take an application down :D.

Re: Having, a less understood SQL clause

#23

Post by xivzgrev »

Can anyone explain why the query without having needs 14 separate queries? That seemed insane to me.

It seems like the author is using one query per country. Where it seems like you’d just group by country and year, where country US

You would need some unions to bolt on the additional aggregations, but it’s more like 4 queues, not 14

Eg

select c.ctry_name, i.year_nbr, sum(i.item_cnt) as tot_cnt, sum(i.invoice_amt) as tot_amt from country c inner join invoice i on (i.ctry_code = c.ctry_code) where c.ctry_name 'USA' group by c.ctry_name, i.year_nbr

Re: Having, a less understood SQL clause

#24

Post by beefield »

Well, I consider myself somewhat fluent with SQL, and for some reason left joins are the ones that occasionally get me really confused - so much so that I actively try to avoid them. Trouble is not in the vanilla cases, but when you start throwing multiple tables and multiple where clauses in the same query, then there is something about left joins and nulls that is really unintuitive to my brain. Maybe I should spend some time and study the left join more...

Re: Having, a less understood SQL clause

#25

Post by larsrc »

teddyh wrote: post #3

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grouped row values. (Obviously, this is only useful if you actually have a GROUP BY clause.) If HAVING did not exist, you could just as well do the same thing using a subselect (i.e. doing SELECT * FROM (SELECT * FROM … WHERE … GROUP BY …) WHERE …; is, IIUC, equiva…

But HAVING can also act on aggregate results. In fact, the example in the article is not the most important use for HAVING. Subselects can't do something like.

SELECT year, COUNT(*) sales, SUM(price) income FROM sales HAVING sales > 10 AND income < 1000;

Re: Having, a less understood SQL clause

#26

Post by cm2187 »

galkk wrote: post #5

Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this article... "group by cube/coalesce" is much more complicated thing in this article than "having" (that could be explained as where but for group by)

Exactly the same for me. It makes me think I need to start reading the TSQL documentation cover to cover.

Re: Having, a less understood SQL clause

#27

Post by p4bl0 »

larsrc wrote: post #25
teddyh wrote: post #3

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grouped row values. (Obviously, this is only useful if you actually have a GROUP BY clause.) If HAVING did not exist, you could just as well do the same thing using a subselect (i.e. doing SELECT * FROM (SELECT * FROM … WHERE … GROUP BY …) WHERE …; is, IIUC, equiva…

But HAVING can also act on aggregate results. In fact, the example in the article is not the most important use for HAVING. Subselects can't do something like. SELECT year, COUNT(*) sales, SUM(price) income FROM sales HAVING sales > 10 AND income < 1000;

Why wouldn't

    SELECT *
    FROM (SELECT year, COUNT(*) as nb_sales, SUM(price) as income
          FROM sales)
    WHERE nb_sales > 10 AND income
work just like your example?

Re: Having, a less understood SQL clause

#28

Post by p4bl0 »

mastax wrote: post #9

I like using HAVING just to have conditions that reference expressions from the SELECT columns. e.g. rather than having to do SELECT COALESCE(extract_district(rm.district), extract_district(p.project_name), NULLIF(rm.district, '')) AS district, ... FROM ... WHERE COALESCE(extract_district(rm.district), extract_district(p.project_name), NULLIF(rm.district, '')) IS NOT NULL just do SELECT COALESCE(extract_district(rm.d…

This should work with WHERE too at least as long as the name given using AS in the SELECT is given to a row and not to an aggregate.

Re: Having, a less understood SQL clause

#29

Post by Linosaurus »

larsrc wrote: post #25
teddyh wrote: post #3

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grouped row values. (Obviously, this is only useful if you actually have a GROUP BY clause.) If HAVING did not exist, you could just as well do the same thing using a subselect (i.e. doing SELECT * FROM (SELECT * FROM … WHERE … GROUP BY …) WHERE …; is, IIUC, equiva…

But HAVING can also act on aggregate results. In fact, the example in the article is not the most important use for HAVING. Subselects can't do something like. SELECT year, COUNT(*) sales, SUM(price) income FROM sales HAVING sales > 10 AND income < 1000;

This should work, right? Just a bit more unnecessary text.

SELECT year, sales, income FROM ( SELECT year, COUNT(*) sales, SUM(price) income FROM sales ) AS innerquery WHERE sales > 10 AND income < 1000;

Re: Having, a less understood SQL clause

#30

Post by Linosaurus »

xivzgrev wrote: post #23

Can anyone explain why the query without having needs 14 separate queries? That seemed insane to me. It seems like the author is using one query per country. Where it seems like you’d just group by country and year, where country US You would need some unions to bolt on the additional aggregations, but it’s more like 4 queues, not 14 Eg select c.ctry_name, i.year_nbr, sum(i.item_cnt) as tot_cnt, sum(i.invoice_amt) as…

> Can anyone explain why the query without having needs 14 separate queries? That seemed insane to me.

Yeah, four queries for the four requirements seems the most straightforward, and easier to maintain than the final version.

But still a very nice illustrating of group by cube.

Post reply on HN