CODEWITHSUNDEEP
mysqlsqlcount
elBadr
elBadr

Reputation: 25

Multiple SELECT COUNT from different tables mySQL

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'  ");

Upvotes: 0

Views: 3403

Answers (1)

Praveen Lobo
Praveen Lobo

Reputation: 7187

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.

Upvotes: 1

Related Questions