Assume I have the following simplified data in BigQuery:

WITH sales_log AS
 (
  SELECT 'John' as employee, 'ABC' client, 1234.56 sales, "phone" sale_type UNION ALL
  SELECT 'John' as employee, 'ABC' client, 9857.56 sales, "online" sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 5678.56 sales, "phone" sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 64875.25 sales, "online" sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 456.58 sales, "phone" sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 11585.58 sales, "online" sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 4578.52 sales, "phone" sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 56853.45 sales, "online" sale_type
  )
SELECT employee, AVG(sales) AS avg_sales
FROM sales_log
GROUP BY employee

I can easily get the average of the employees' sales.

Is there an easy way to also get the average of EACH TYPE of sale in a single row? So that the output would be like:

Thank you in advance.

CodePudding user response：

Couple approaches:

• DEMO's at bottom:
• If the Types are limited you can use case expressions for each column.
• If they are "Dynamic" then you need a dynamic pivot. (Several online examples exist; but this means dynamic SQL which can be prone to SQL injection)
• NOTE: Typically data formatting such as this is done in the UI not the SQL.
• NOTE: Use of Null ensures the "blank" values don't impact your average.

.

WITH sales_log AS
 (
  SELECT 'John' as employee, 'ABC' client, 1234.56 sales, 'phone' sale_type UNION ALL
  SELECT 'John' as employee, 'ABC' client, 9857.56 sales, 'online' sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 5678.56 sales, 'phone' sale_type UNION ALL
  SELECT 'John' as employee, 'XYZ' client, 64875.25 sales, 'online' sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 456.58 sales, 'phone' sale_type UNION ALL
  SELECT 'Mary' as employee, 'ABC' client, 11585.58 sales, 'online' sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 4578.52 sales, 'phone' sale_type UNION ALL
  SELECT 'Mary' as employee, 'XYZ' client, 56853.45 sales, 'online' sale_type
  )
SELECT employee, AVG(sales) AS avg_sales, 
       AVG(case when sale_type = 'phone' then sales else NULL end) as AVG_PhoneSales, 
       AVG(case when sale_type = 'online' then sales else NULL end) as AVG_OnLineSales
FROM sales_log
GROUP BY employee

GIVING US:

 ---------- -------------------- ----------------------- -------------------- 
| employee |     avg_sales      |  AVG_phonesales       | AVG_onlinesales    |
 ---------- -------------------- ----------------------- -------------------- 
| Mary     | 18368.532500000000 | 2517.5500000000000000 | 34219.515000000000 |
| John     | 20411.482500000000 | 3456.5600000000000000 | 37366.405000000000 |
 ---------- -------------------- ----------------------- --------------------