I use this all the time to find dups:
Select pk columns + count(column that might have dups) From table Group by pk Having count(*) > 1
Having, a less understood SQL clause
11–20 of 83 posts
Re: Having, a less understood SQL clause
#12Having 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
Re: Having, a less understood SQL clause
#13Having 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)
I had never heard of GROUP BY CUBE either! It looks like it's part of a family of special GROUP BY operators—GROUPING SETS, CUBE, and ROLLUP—that basically issue the same query multiple times with different GROUP BY expressions and UNION the results together.
Using GROUP BY CUBE(a, b, c, ...) creates GROUP BY expressions for every element in the power set of {a, b, c, ...}, so GROUP BY CUBE(a, b) does separate GROUP BYs for (a, b), (a), (b) and ().
It's like SQL's version of a pivot table, returning aggregations of data filtered along multiple dimensions, and then also the aggregations of those aggregations.
It seems like it's well supported by Postgres [1], SQL Server [2] and Oracle [3], but MySQL only has partial support for ROLLUP with a different syntax [4].
[1]: https://www.postgresql.org/docs/current/queries-table-expres...
[2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-gr...
[3]: https://oracle-base.com/articles/misc/rollup-cube-grouping-f...
[4]: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.h...
Re: Having, a less understood SQL clause
#14I think that when doing these kind of report style queries cubed/rolled up columns becomes labels and loses their data type. So it makes sense to convert+coalesce the year column as well to get 'All years' instead of the hard to interpret "null year".
Re: Having, a less understood SQL clause
#15Having 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)
Same!
I’ve been a developer for 15 years, consider myself pretty good with SQL, but I’m only now learning about group by cube.
Re: Having, a less understood SQL clause
#16Having 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
they’re just self joins on views of the query.
Re: Having, a less understood SQL clause
#17Having 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.
Re: Having, a less understood SQL clause
#18Having 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)
Heard of group by cube before, even tried it in production, but it was a total dog of a query to run, so I filed it away to try again in a few years.
Re: Having, a less understood SQL clause
#19Having is the where-clause for Group By. It's easier to understand by thinking the SQL query as a pipeline.
Stage 1: From returns the whole world of rows.
Stage 2: Where filters down to the desired set of rows.
Stage 3: Group By aggregates the filtered rows.
Stage 4: Having filters again on the aggregated result.
Stage 5: Select picks out the columns.
Re: Having, a less understood SQL clause
#20Having vs where is my first question to filter candidates who have less experience in sql than they claim.