Search code examples
sqlgroup-bywhere-clausehaving

Is WHERE and HAVING clause possible without GROUP BY clause in SQL?

Suppose I'm writing as query for the aggregate functions where I want result based on some conditions both on the column of the table and on aggregate function. So is it possible to use WHERE and HAVING clause to get expected result without GROUP BY clause.

I wrote following query for the above condition.

select *
from ORDER_DETAILS 
where item_price > 1000
having count(item) >= 5 ;
Solution

  • First of all, having just like where, but can apply to aggregate function results.
    You should keep track of the data rows and columns after each clause.
    If we name a row_id property that can be used to locate one single row of a table. Then the where clause doesn't change the row_id.
    When we use aggregate functions, it implies input multi rows and get a single result, that changes the row_id.In fact no group by clause means everything go to one bucket, and the output result only have one row.
    My best guess is that you want to get original data rows, which have some attributes that passes aggregated value check.Eg found order details that item price>1000(origin filter) and more than 5 items in single order(aggregated filter).
    So group + aggregate + having give you aggregated filter dataset, you can join this dataset back to origin table, then the result table have same row_id with original ORDER_DETAILS

    select *
from ORDER_DETAILS 
where item_price > 1000
and order_id in (
  select order_id 
  from ORDER_DETAILS 
  group by order_id 
  having count(item) >= 5
);

    Note：

    • order_id is the aggregated filter column example
    • I use in subquery for convenience, you can change it into join
    • If you are working with big data sql, like hive/spark, you can also use window functions to get the aggregate result on each row of original table.