GROUP BY / aggregate function confusion in SQL
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.)
SQL group by functionality confusion
The GROUP BY clause is used to limit an aggregate function (of which count() is only one of several) to a certain range. Without a group clause the aggregate would operate on all values in a table (further determined by a WHERE clause, if necessary). Grouping first splits the rows in a table/view into individual sets determined by group clause (all those entries belong to one group that have the same value, specified by the GROUP BY clause) and then applies the aggregate to each of them.
Example:
Assume you have a table with people, departments and income. You can now determine the sum of all income (or max, or min etc.) by running
select sum(income) from people;
Which returns a single value: the sum of all income.
For individual departments you can then group the result:
select department, sum(income) from people group by department;
This returns as many rows as there are departments and a sum of all income per department. Read more here: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html along with a list of possible aggregate functions.
Btw. Don't let people confuse you by statements like "group is mostly used with count()" which is plain wrong.
SQL confusion with the groupby function
use all the selection column in group by also as you used aggregate function
SELECT customers.CompanyName,
customers.ContactName,
customers.phone,
customers.country,
SUM(orders.Freight) AS Total_Freight
FROM CUSTOMERS INNER JOIN ORDERS
ON customers.CustomerID = ORDERS.customerID
WHERE orders.orderDate BETWEEN '1993-07-01' AND '1993-08-31'
GROUP BY customers.CompanyName,
customers.ContactName,
customers.phone,
customers.country
ORDER BY customers.companyName
How to Use Group By clause when we use Aggregate function in the Joins?
GROUP BY
for any unique combination of the specified columns does aggregation (like sum, min etc). If you don't specify some column name in the GROUP BY
clause or in the aggregate function its unknown to the SQL engine which value it should return for that kind of column.
Confusion with aggregate function
- Yes
- Yes, but you must use an aggregate with HAVING, it is optional with a regular SELECT statement.
- Yes, it wouldn't make sense to interrogate "salary" without an aggregate function, as using the HAVING clause indicates you have already aggregated above the individual row level.
Oracle group by with different condition for each aggregate function
You can try below - using case when
expression
select order, count(case when order_price > 100 then order end),
sum(case when order_id < 200 then order_price end),
avg(case when other condition then order_price end)
from table_orders
group by order
How to solve error Result: misuse of aggregate function json_group_array()?
You can't nest an aggregate functions like json_group_array()
inside another.
Maybe you want to aggregate on the results of the query:
SELECT json_object('a', json_group_array(json_result)) json
FROM (
SELECT json_object(
'b', rm.id,
'c', '{}',
'd', l.code,
'details',
json_group_array(
json_object('e', rmd.id, 'f', rmd.s_id, 'g', s.name)
)
) json_result
FROM ....
)
Related Topics
Difference Between Join and Inner Join
Does Postgresql Support "Accent Insensitive" Collations
MySQL - How to Unpivot Columns to Rows
MySQL Insert Query Doesn't Work With Where Clause
Quick Selection of a Random Row from a Large Table in MySQL
Table Naming Dilemma: Singular Vs. Plural Names
How to Update Only One Field Using Entity Framework
MySQL Error:: 'Access Denied For User 'Root'@'Localhost'
Auto Increment After Delete in MySQL
SQL Statement Is Ignoring Where Parameter
Error 1452: Cannot Add or Update a Child Row: a Foreign Key Constraint Fails
SQL Join and Different Types of Joins
How to Select Rows With Max(Column Value), Partition by Another Column in MySQL
How to Update If Exists, Insert If Not (Aka "Upsert" or "Merge") in MySQL