Concept of <ALL> type in SQL?-CodePudding

Obviously every SQL dialect has the concept of NULL. However, I'm wondering if any support the concept of an ALL type? Let me give one example where I think it's quite useful:

SELECT content_type_id, count(*) FROM movies
GROUP BY content_type_id WITH ROLLUP

content_type_id count(*)
NULL            1
Movie           45014
Movie Bundle    865
Other           62
TV Series       66
NULL            151984      # this should be our ALL value

Now, I know that there is the GROUPING function which can be for. From the MySQL docs:

For GROUP BY queries that include a WITH ROLLUP modifier, the ROLLUP operation produces super-aggregate output rows where NULL represents the set of all values. The GROUPING() function enables you to distinguish NULL values for super-aggregate rows from NULL values in regular grouped rows.

However, this seems a bit hackish and it sounds like it'd be much more appropriate to just have an ALL value, especially if there are multiple levels of aggregation, where it becomes quite tedious to keep track of! Does something like that existing in SQL?

Update: any SQL dialect is fine. I'm curious more in a general sense if any db's have this concept or the sql standard itself.

CodePudding user response：

There is no value in SQL that means "ALL." But if it bothers you to have a NULL in the grouping column in the query you show, you can do this:

SELECT COALESCE(content_type_id, 'ALL') AS content_type_id, COUNT(*) 
FROM movies
GROUP BY content_type_id WITH ROLLUP

CodePudding user response：

especially if there are multiple levels of aggregation, where it becomes quite tedious to keep track of

If you just want to do this, you can use ‘union all’, which is simpler and compatible with all sql dialects

SELECT content_type_id, count(*) FROM movies
GROUP BY content_type_id 
union all
SELECT 'all', count(*) FROM movies

The 'all' operator exists in some dialects such as Oracle. I think this is a very bad syntax, because it is likely to hide the intent of SQL. But it might be different from what you think?

select 1 from dual where 3 > all(1, 2, 3)