sqloracle-databaseoracle11g

Rows of table variables and columns of counts, min and max?


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?


Solution

  • 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