GROUP BY, HAVING, ROLLUP/CUBE/GROUPING SETS

Oracle

-- Basic grouping + HAVING
SELECT city, COUNT(*) AS n
FROM customer
GROUP BY city
HAVING COUNT(*) > 5;

-- Subtotals and grand total with ROLLUP
SELECT city, district, SUM(balance)
FROM customer
GROUP BY ROLLUP(city, district);

GROUP BY groups rows by the given columns and applies aggregate functions; HAVING filters the grouped result (WHERE filters rows before grouping). ROLLUP produces hierarchical subtotals + a grand total, CUBE all combinations, and GROUPING SETS the selected groupings. Oracle supports all three.

PostgreSQL

SELECT city, district, SUM(balance)
FROM customer
GROUP BY GROUPING SETS ((city, district), (city), ());

-- CUBE produces all subset combinations
SELECT city, district, SUM(balance)
FROM customer
GROUP BY CUBE(city, district);

PostgreSQL 9.5+ supports ROLLUP, CUBE and GROUPING SETS in a standard-compliant way. The GROUPING() function tells whether a column is in a total row (NULL). HAVING filters after aggregation, WHERE before.

MySQL

SELECT city, COUNT(*) AS n
FROM customer
GROUP BY city
HAVING n > 5;

-- ROLLUP in MySQL:
SELECT city, district, SUM(balance)
FROM customer
GROUP BY city, district WITH ROLLUP;

MySQL/MariaDB produce subtotals/grand totals with GROUP BY ... WITH ROLLUP syntax; CUBE and GROUPING SETS are not supported. SELECT aliases can be used in HAVING (this generally does not work in Oracle/SQL Server). In ROLLUP rows the grouped column appears as NULL.

SQL Server

SELECT city, COUNT(*) AS n
FROM customer
GROUP BY city
HAVING COUNT(*) > 5;

-- ROLLUP / CUBE / GROUPING SETS
SELECT city, district, SUM(balance)
FROM customer
GROUP BY ROLLUP(city, district);

Alongside GROUP BY, SQL Server supports ROLLUP(), CUBE() and GROUPING SETS (2008+) with modern syntax; the old WITH ROLLUP/WITH CUBE form is deprecated. GROUPING() and GROUPING_ID() help distinguish total rows.

MongoDB

// Closest equivalent: $group + $sum
db.customer.aggregate([
  { $group: { _id: "$city", n: { $sum: 1 } } },
  { $match: { n: { $gt: 5 } } }
])
// $facet is used for ROLLUP-like multi-level totals

In MongoDB $group is the equivalent of SQL GROUP BY; _id sets the grouping key and accumulators ($sum, $avg, $max) define the aggregates. Filtering after grouping is done with $match (the HAVING equivalent). Multi-level totals such as ROLLUP/CUBE are obtained by running multiple pipelines with $facet.

Redis

# Not applicable: core Redis has no GROUP BY.
# Aggregation with RediSearch:
FT.AGGREGATE customer_idx "*" GROUPBY 1 @city REDUCE COUNT 0 AS n

Core Redis offers no grouping; the RediSearch module's FT.AGGREGATE command performs SQL-like aggregation with GROUPBY and REDUCE (COUNT, SUM, AVG). The FILTER step filters the grouped result like HAVING. Without the module, aggregation is done in the application.

Elasticsearch

GET customer/_search
{
  "size": 0,
  "aggs": {
    "by_city": { "terms": { "field": "city" },
      "aggs": { "total": { "sum": { "field": "balance" } } } }
  }
}

In Elasticsearch grouping is done with the 'terms' bucket aggregation and aggregates with metric aggregations (sum, avg, cardinality). For HAVING-like filtering, use the 'bucket_selector' pipeline aggregation. Multi-level subtotals are built with nested aggregations.