Return aggregate results per column in SQL

Question

Suppose I have a table like so:

ID	Purchases	Quantity	Products
001	5	7	2
002	12	24	9

Is there a way in SQL where I can return aggregate information about each column like so:

Column	Min	Max	Avg	Count
Purchases	5	12	6.5	2
Quantity	7	24	14.6	3
Products	2	9	3.7	4

I can define a list of columns I want to return metrics on with DECLARE

DECLARE @METRICS VARCHAR(8000);
SET @METRICS = 'Purchases, Quantity', 'Products';
SELECT @METRICS AS ColumnNames;

But then how to write a SELECT statement that returns the aggregated results?

I thought I could do something like this based on @ggordon response here https://stackoverflow.com/a/69600079/2430558, but my declared variable is a list

DECLARE @MyQuery NVARCHAR(4000) = CONCAT(N'
    SELECT 
    AVG(', @METRICS, N') AS Col_Avg
    FROM mytable GROUP BY', @METRICS);

EXECUTE SP_EXECUTESQL @MyQuery;

Your query doesn't make much sense as it stands: you are grouping and aggregating by the same columns — Charlieface, Jul 20 '23 at 19:07

Jonas Metzler · Answer 1 · 2023-07-20T07:31:42.563

One option is to use separate queries for each row and combine them to one result with UNION ALL:

SELECT
  'Purchases' AS "column",
  MIN(Purchases) AS min,
  MAX(Purchases) AS max,
  ROUND(AVG(Purchases),2) AS avg,
  COUNT(Purchases) AS count
FROM mytable
UNION ALL
SELECT
  'Quantity' AS "column",
  MIN(Quantity) AS min,
  MAX(Quantity) AS max,
  ROUND(AVG(Quantity),2) AS avg,
  COUNT(Quantity) AS count
FROM mytable
UNION ALL
SELECT
  'Products' AS "column",
  MIN(Products) AS min,
  MAX(Products) AS max,
  ROUND(AVG(Products),2) AS avg,
  COUNT(Products) AS count
FROM mytable;

This will produce from your sample data...

ID	Purchases	Quantity	Products
001	5	7	2
002	12	24	9

...following result:

column	min	max	avg	count
Purchases	5	12	8.50	2
Quantity	7	24	15.50	2
Products	2	9	5.50	2

I don't know how you will get different values in the column "count" from your sample data. I guess this will happen if some of the columns are NULL. See this fiddle example. I added another row to the sample data where the Quantity is NULL. Then the result will be this one:

column	min	max	avg	count
Purchases	5	12	9.00	3
Quantity	7	24	15.50	2
Products	2	20	10.33	3

Formatting queries makes them much easier to read and understand — Dale K, Jul 20 '23 at 07:26
Hi Jonas, thanks for this, but while this is a solution to the specific table, what if I wanted to apply this query to another table? I would need to add another union module and replace the column name in all the aggregates - MIN(), MAX(), AVG(), etc... I'm looking for something where I can just change a list of columns in one place — DrPaulVella, Jul 20 '23 at 07:27
@DrPaulVella In my opinion, that is another question/another step and should be answered in a separate question. Please keep in mind the guidelines here say one question per question. This makes it easier for other people with similar questions to find solutions for their issues. — Jonas Metzler, Jul 20 '23 at 07:36

Return aggregate results per column in SQL

1 Answers1