GROUP BY / aggregate function confusion in SQL

by

In standard SQL (but not MySQL), when you use GROUP BY, you must list all the result columns that are not aggregates in the GROUP BY clause. So, if order_details has 6 columns, then you must list all 6 columns (by name – you can’t use * in the GROUP BY or ORDER BY clauses) in the GROUP BY clause.

You can also do:

SELECT order_no, SUM(order_price)
  FROM order_details
 GROUP BY order_no;

That will work because all the non-aggregate columns are listed in the GROUP BY clause.

You could do something like:

SELECT order_no, order_price, MAX(order_item)
  FROM order_details
 GROUP BY order_no, order_price;

This query isn’t really meaningful (or most probably isn’t meaningful), but it will ‘work’. It will list each separate order number and order price combination, and will give the maximum order item (number) associated with that price. If all the items in an order have distinct prices, you’ll end up with groups of one row each. OTOH, if there are several items in the order at the same price (say £0.99 each), then it will group those together and return the maximum order item number at that price. (I’m assuming the table has a primary key on (order_no, order_item) where the first item in the order has order_item = 1, the second item is 2, etc.)

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. SQL Query to concatenate column values from multiple rows in Oracle
  3. Select First Row of Every Group in sql [duplicate]
  4. Oracle: how to UPSERT (update or insert into a table?)
  5. Paging with Oracle
  6. Comparing Dates in Oracle SQL
  7. Oracle Differences between NVL and Coalesce
  8. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  9. SQL – How to store and navigate hierarchies?
  10. Is it possible to output a SELECT statement from a PL/SQL block?
  11. Oracle SQL, concatenate multiple columns + add text
  12. Oracle query to fetch column names
  13. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  14. SQL Error: ORA-01861: literal does not match format string 01861
  15. Determine Oracle null == null
  16. How to anticipate and escape single quote ‘ in oracle
  17. Oracle insert if row does not exist
  18. SELECT INTO using Oracle
  19. Display names of all constraints for a table in Oracle SQL
  20. `show create table` equivalent in oracle sql
  21. Does ‘Select’ always order by primary key?
  22. Query json dictionary data in SQL
  23. ORA-00972 identifier is too long alias column name
  24. Search an Oracle database for tables with specific column names?
  25. How to replace specific values in a oracle database column?
  26. ORA-01861: literal does not match format string
  27. Query a Table’s Foreign Key relationships
  28. Backup SQL Schema Only?
  29. IN vs OR of Oracle, which faster?
  30. is there a PRODUCT function like there is a SUM function in Oracle SQL?

Leave a Comment