I have 3 columns in my table of charges like
CHRG_ACCOM | CHRG_ANCIL | CHRG_TOT |
---|---|---|
100.00 | 50.00 | 150.00 |
300.00 | 150.00 | 450.00 |
100.00 | 50.00 | 150.00 |
... | ... | ... |
I need row counts, min value and max value for each of these columns. My desired output would be one table formatted like so
COUNT | MIN | MAX | |
---|---|---|---|
CHRG_ACCOM | |||
CHRG_ANCIL | |||
CHRG_TOT |
It is easy to go variable by variable
SELECT COUNT(CHRG_ACCOM_AMT) AS CNT,
MIN(CHRG_ACCOM_AMT) AS MIN,
MAX(CHRG_ACCOM_AMT) AS MAX
FROM Tb1
But how can I pivot to get the desired output?
You can use UNION
or UNION ALL
:
SELECT 'CHRG_ACCOM' AS "Name", COUNT(1) AS "COUNT", MIN(CHRG_ACCOM) AS "MIN", MAX(CHRG_ACCOM) AS "MAX" FROM Tbl
UNION ALL
SELECT 'CHRG_ANCIL' AS "Name", COUNT(1) AS "COUNT", MIN(CHRG_ANCIL) AS "MIN", MAX(CHRG_ANCIL) AS "MAX" FROM Tbl
UNION ALL
SELECT 'CHRG_TOT' AS "Name", COUNT(1) AS "COUNT", MIN(CHRG_TOT) AS "MIN", MAX(CHRG_TOT) AS "MAX" FROM Tbl