Multiple SELECT COUNT from different tables mySQL

Question

I want to count the number of images with prefix PublicImg from different two tables, so I used this code:

    $nb_imgs_a_afficher = $BDD->query("
                                    SELECT COUNT(ID,img_kind) 
                                    FROM images_public  

                                    UNION

                                    SELECT COUNT(ID,img_kind) 
                                    FROM images_users  

                                    WHERE img_kind='PublicImg'");

    $images_users = $nb_imgs_a_afficher->fetch();

But I receive this error:

( ! ) Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' in C:\wamp1\www\website\Index_Public_Images\public_images.php on line 72

Well, before I had one table that was easy with:

$nb_imgs_a_afficher = $BDD->query("SELECT COUNT(*) AS nb_imgs FROM images_users WHERE img_kind='PublicImg'  ");

SELECT COUNT ... + SELECT COUNT ... ? I think you need the plus operator (+) instead of UNION — Kofi Sarfo
– Kofi Sarfo, Commented Sep 17, 2013 at 15:38

Praveen Lobo · Accepted Answer · 2013-09-17 15:48:06Z

1

Firstly, you have a typo there. Change SLECET to SELECT and then if you are trying to get a sum of rows from two different table use -

SELECT COUNT(*) FROM (
    SELECT ID,img_kind
            FROM images_public  
            UNION
    SELECT ID,img_kind
            FROM images_users  
            WHERE img_kind='PublicImg' ) x

Not sure if you meant to have the WHERE clause on both queries. If so, add WHERE img_kind='PublicImg' before the UNION as well.

Also, check for duplicates. UNION removes duplicates where as UNION ALL doesn't.

edited Sep 17, 2013 at 15:48

answered Sep 17, 2013 at 15:42

Praveen Lobo

7,2072 gold badges31 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

Multiple SELECT COUNT from different tables mySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related