SQL Count Statement with multiple tables-CodePudding

With the below count queries I will like to have them all appear on one screen with a separator (-----------).

SELECT count(*) as Count_F3112 FROM PRODDTA.F3112; --> WO Routing
SELECT count(*) as Count_F4801 FROM PRODDTA.F4801; --> WO Header (Master File)
SELECT count(*) as Count_F0006 FROM PRODDTA.F0006; --> Business Unit Master
SELECT count(*) as Count_F0101 FROM PRODDTA.F0101; --> Address Book Master

SAMPLE

I executed the statements and the lines were separated into individual query results.

CodePudding user response：

You could use a union query:

SELECT COUNT(*) AS cnt, 'WO Routing' AS label FROM PRODDTA.F3112
UNION ALL
SELECT COUNT(*), 'WO Header (Master File)' FROM PRODDTA.F4801
UNION ALL
SELECT COUNT(*), 'Business Unit Master' FROM PRODDTA.F0006
UNION ALL
SELECT COUNT(*), 'Address Book Master' FROM PRODDTA.F0101;

CodePudding user response：

user sparator from select only '-----'

SELECT count(*) as Count_F3112 FROM PRODDTA.F3112
    UNION ALL
    SELECT '-----------'
    UNION ALL
    SELECT count(*) as Count_F4801 FROM PRODDTA.F4801
    UNION ALL
    SELECT '-----------'
    UNION ALL
    SELECT count(*) as Count_F0006 FROM PRODDTA.F0006
    UNION ALL
    SELECT '-----------'
    SELECT count(*) as Count_F0101 FROM PRODDTA.F0101

CodePudding user response：

You can use a UNION!

SELECT count(*) as Count_F3112 FROM PRODDTA.F3112 UNION
SELECT count(*) as Count_F4801 FROM PRODDTA.F4801 UNION
SELECT count(*) as Count_F0006 FROM PRODDTA.F0006 UNION
SELECT count(*) as Count_F0101 FROM PRODDTA.F0101;