SQL Count Statement with multiple tables

Question

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.

Whats the database type? SQL Server, Postgres, MySQL?

Jeremy Thompson
– Jeremy Thompson

2022-12-12 04:46:28 +00:00
Commented Dec 12, 2022 at 4:46 — Jeremy Thompson
– Jeremy Thompson, Commented Dec 12, 2022 at 4:46

Tim Biegeleisen · Accepted Answer · 2022-12-12 04:17:38Z

1

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;

answered Dec 12, 2022 at 4:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kucluk · Accepted Answer · 2022-12-12 04:16:22Z

0

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

answered Dec 12, 2022 at 4:16

kucluk

5492 gold badges12 silver badges34 bronze badges

2 Comments

tinazmu Over a year ago

depending on the database used this can error, because the columns are not the same type ('---' is string, but count(*) is numeric). Also the orde rof the results are not guaranteed

kucluk Over a year ago

yes , you can use Cast first befor count, cast (count(*) as nvarchar)

Jeremy Thompson · Accepted Answer · 2022-12-12 04:16:22Z

0

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;

answered Dec 12, 2022 at 4:16

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

2 Comments

tinazmu Over a year ago

union can be risky if the counts from two separate tables have the same count; UNION ALL would be better, but the OP asks for a report anyway, not just 4 numbers

Jeremy Thompson Over a year ago

I've asked the OP to clarify the database type.

Collectives™ on Stack Overflow

SQL Count Statement with multiple tables

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related