Select 3 tables and count each tables rows all in one statement

Databases
1

Hi,

I have 3 tables, users, sales and items. Right now I am doing 3 sql statements at once to get the data but was wondering how i do a statement so that it counts them all individually but in one statement.

So this:

SELECT count(*) AS total FROM sales WHERE status = 1
SELECT count(*) AS total FROM items WHERE status = 1
SELECT count(*) AS total FROM users WHERE status IN (1,2)

Merged all together to make one statement where the counters are assigned as total_sold, total_items and total_users.

How would i go about doing this? Thanks… Could i use union? (sql count statement) + (sql count statement)? I’m unsure. Thanks

2 
SELECT 'total_sold' AS total_type
     , COUNT(*)     AS total 
  FROM sales 
 WHERE status = 1
UNION ALL
SELECT 'total_items'
     , COUNT(*)
  FROM items 
 WHERE status = 1
UNION ALL
SELECT 'total_users'
     , COUNT(*) 
  FROM users 
 WHERE status IN (1,2)
3

Hi, thanks for your answer but mySQL said
#1054 - Unknown column 'total_sold' in 'field list'

Thanks

4

This worked tho:

SELECT  (
    SELECT COUNT(*)
    FROM chewi1_m_sales
    WHERE status = 1
    ) AS total_sales,
    (
    SELECT COUNT(*)
    FROM chewi1_m_items
    WHERE status = 1
    ) AS total_items,
    (
    SELECT COUNT(*)
    FROM chewi1_users
    WHERE status IN (1,2)
    ) AS total_users
5

that’s unpossible, you obviously were running something other than the query i gave you

6

well, yeah… but you said “Could i use union?”

7

Oh sorry, no that was just ideas.

See: Could i use union? (sql count statement) + (sql count statement)?

but ye thanks anyway. I tried your method but PHPMYADMIN did not like it and produced an error.

Thanks

8

You posted:

The UNION ALL query used those as the field names in the SELECTs.

If those are not the actual field names, substituting the correct ones into the query should work without error.

EDIT
:blush: Need. More. Coffee. coffee

9

don’t blame phpmyadmin, it was you who messed it up somehow, my friend :slight_smile:

10

no it didn’t, sorry – the only field name used in my query was status

11

If you take @r937’s SQL but don’t quote the strings, i.e.:

SELECT total_sold AS total_type -- Note: total_sold instead of 'total_sold'
     , COUNT(*)     AS total 
  FROM sales 
 WHERE status = 1
UNION ALL
SELECT total_items -- Note: total_items instead of 'total_items'
     , COUNT(*)
  FROM items 
 WHERE status = 1
UNION ALL
SELECT total_users -- Note: total_users instead of 'total_users'
     , COUNT(*) 
  FROM users 
 WHERE status IN (1,2)

You would get exactly this error #1054 - Unknown column 'total_sold' in 'field list'
Which is also the error @jack55 got.

Just sayin’ :upside_down_face:

12

exackly

i mean, why would someone take the quotes off a string?

13

You were right. Thanks

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.