CODEWITHSUNDEEP
phpmysqlsql
Ace
Ace

Reputation: 845

Selecting SUM+COUNT from one table and COUNT from another in Single query

I want to fetch Sum of age and total users which are active from users table and total number of png pictures from pictures table. I tried the following but it is giving me error. How can i do this in single query? I'm using PDO

SELECT
(SELECT SUM(age) AS age_sum,COUNT(*) AS total FROM users where type='active'),
(SELECT COUNT(*) AS pictotal FROM pictures where extension='png')

ERROR -> Operand should contain 1 column(s)

Upvotes: 1

Views: 684

Answers (4)

Floyd
Floyd

Reputation: 401

SELECT
AA.age_sum, AA. total, BB.pictotal
FROM
(SELECT SUM(age) AS age_sum,COUNT(*) AS total FROM users where type='active') AA,
(SELECT COUNT(*) AS pictotal FROM pictures where extension='png') BB

Upvotes: 4

BWS
BWS

Reputation: 3846

You could try something like this:

SELECT 'AGE_SUM',SUM(age) FROM users where type='active'
UNION
SELECT 'TOTAL',COUNT(*) FROM users where type='active'
UNION
SELECT 'PIC_TOTAL',COUNT(*) FROM pictures where extension='png'

Upvotes: 1

Valentin Petkov
Valentin Petkov

Reputation: 1648

You did it This select is your problem returns 2 columns
(SELECT SUM(age) AS age_sum,COUNT(*) AS total 

 SELECT SUM(age) AS age_sum,COUNT(*) AS total, 
 (SELECT COUNT(*) FROM pictures where extension='png') AS pictotal
 FROM users where type='active'

Upvotes: 1

ins0
ins0

Reputation: 3928

For your task is a Sub Select the better decision - like this

SELECT SUM(age) AS age_sum,COUNT(*) AS total, (SELECT COUNT(*) FROM pictures where extension='png') AS pictotal FROM users where type='active')

You simple can't "merge" two different querys in one query when the amount of columns, that are returned from each query, are not equal.

Upvotes: 1

Related Questions