CODEWITHSUNDEEP
mysql
SilentCry
SilentCry

Reputation: 2092

Mysql - select from two table as two values

I cannot understand why my query

SELECT SUM(DATE(date) = '2014-11-2') AS `rate` FROM `ratings` UNION SELECT COUNT(*) AS `users` FROM `user`

doesn't create two values - rate, users as in query but only one - rate - with two values.

Result:

rate 
6 
10

Expected result:

rate users
6    10

Do you know, what's wrong with my query?

Upvotes: 0

Views: 41

Answers (3)

MoZoY
MoZoY

Reputation: 49

You should do a JOIN instead of an UNION.

Based on the information you shared and the result you want to achieve, your query could look something like this:

SELECT SUM(DATE(date) = '2014-11-2') AS `rate`, (
    SELECT COUNT(*) FROM `user`
)  AS `users`
FROM `ratings`

...but this lacks of some relations/conditions between the too tables

Upvotes: 1

user3522371
user3522371

Reputation: 

SELECT (COUNT(DATE(date) = '2014-11-2') AS `rate` 
FROM `ratings`), (COUNT(*) AS `users` FROM `user`)

Upvotes: 0

Chief Wiggum
Chief Wiggum

Reputation: 2934

Because union is used to 'collect' several rows from different queries into 1 result set:

Something like this might do the trick:

SELECT (SUM(DATE(date) = '2014-11-2') AS `rate` FROM `ratings`)
, (COUNT(*) AS `users` FROM `user`)

Does that work?

Upvotes: 1

Related Questions