How to aggregate data (or use a GROUP BY clause) without collapsing other columns?-CodePudding

One example is I want to know which items cost more than the average price. So for the original table below:

Item	Prices
A	3
B	2

The average price is 2.5, so my query would ideally select a third column AVG(Prices) as avg then I'd add a HAVING Prices > avg clause at the end. So the selected columns would look more like

Item	Prices	avg
A	3	2.5
B	2	2.5

The problem is, when I use an aggregate like AVG() it collapses the database into one row. Is there a way to only aggregate or GROUP BY one column then broadcasting to the number of rows?

CodePudding user response：

Depending on your dbms it probably supports window functions like this

select *,
       avg(price) over 
         ( partition by 1 ) as average_price
  from your_table

select *,
       (select avg(price) 
          from your_table
       ) as average_price
  from your_table

CodePudding user response：

You could create a procedure to calculate the mean first and then to show only the records with higher prices, together with the mean value as a column.

CREATE PROCEDURE More_Expensive_than_Average()
BEGIN
    DECLARE @meanpr DECIMAL;
    SELECT AVG(Prices) INTO @meanpr FROM TABLE_NAME;
    SELECT Item, Prices, @meanpr AS average
    WHERE Prices > @meanpr;
END;