CODEWITHSUNDEEP
sql
aseipel
aseipel

Reputation: 728

SQL aggregate group by columns

I have a table which looks like this:

price is_a is_b is_c ...
  300    1    0    1 ...
  500    0    1    0 ...
  200    1    1    1 ...
  400    0    1    1 ...

I now want to select the average price for each of the "is_" properties:

avg(price)    is_a     is_b     is_c
  250         1        0        0
  367         0        1        0
  300         0        0        1

I'm currently using the following query, which obviously returns all possible combinations of all properties (in this case, the exact same table as above):

SELECT avg(price), is_a, is_b, is_c, ... FROM table GROUP BY is_a, is_b, is_c, ...

Upvotes: 0

Views: 46

Answers (2)

Mansoor
Mansoor

Reputation: 4192

Use CTE to get your result :

CREATE TABLE #table(price INT,is_a INT,is_b INT,is_c INT)
INSERT INTO #table(price ,is_a ,is_b ,is_c)
SELECT 300,1,0,1 UNION ALL
SELECT 500,0,1,0 UNION ALL
SELECT 200,1,1,1 UNION ALL
SELECT 400,0,1,1 

;WITH _CTEAvg ( Avgprice , is_a , is_b , is_c ) AS 
(
  SELECT AVG(price) , 1 , 0 , 0
  FROM #table
  WHERE is_a = 1
  GROUP BY is_a
  UNION ALL
  SELECT AVG(price) , 0 , 1 , 0
  FROM #table
  WHERE is_b = 1
  GROUP BY is_b
  UNION ALL
  SELECT AVG(price) , 0 , 0 , 1
  FROM #table
  WHERE is_c = 1
  GROUP BY is_c
 )

 SELECT * FROM _CTEAvg

Upvotes: 0

Psi
Psi

Reputation: 6783

You would need to use a union. However, you will be counting some occurences twice:

SELECT avg(price), 1, 0, 0 ... FROM table WHERE is_a = 1
UNION
SELECT avg(price), 0, 1, 0 ... FROM table WHERE is_b = 1
UNION
SELECT avg(price), 0, 0, 1 ... FROM table WHERE is_c = 1

As I said, if there are records that match is_a and is_b, their price will be counted in the first and in the second column.

Upvotes: 2

Related Questions