CODEWITHSUNDEEP
mysqldatabase
victorsxbr
victorsxbr

Reputation: 64

Select and JOIN count from two similar queries/tables

I have two or more tables and want to query the count of each together. These two tables have the same structure.

Here is my query to get the count of each:

SELECT count(*) FROM pt_test_orders 
WHERE payment_log = '100 - Success' AND DATE(created_at) = CURDATE()

SELECT count(*) FROM bs_test_orders 
WHERE payment_log = '100 - Success' AND DATE(created_at) = CURDATE()

if possible, I'm looking to get a result like this:

| table_name          | count |
-------------------------------
|     pt_test_orders  |     3 |
|     bs_test_orders  |     8 |

Any help is appreciated.

Upvotes: 0

Views: 25

Answers (1)

isaace
isaace

Reputation: 3429

Use UNION

SELECT 'pt_test_orders' as TableName, count(*) FROM pt_test_orders 
WHERE payment_log = '100 - Success' AND DATE(created_at) = CURDATE()
UNION  
SELECT 'bs_test_orders'  as TableName, count(*) FROM bs_test_orders 
WHERE payment_log = '100 - Success' AND DATE(created_at) = CURDATE()

Upvotes: 1

Related Questions