开发者

SUM using sub-query

开发者 https://www.devze.com 2023-04-02 06:53 出处:网络
I would like using SELECT, inside SELECT to get some values (i allready done this). Problem is,i would like to display this data in mysql, sum, and that\'s the problem.

I would like using SELECT, inside SELECT to get some values (i allready done this).

Problem is, i would like to display this data in mysql, sum, and that's the problem.

I won't post original code, but look at this example.

SELECT id, (SELECT COUNT(x) FROM xyz where id=usr.id) as value_1,
(SELECT COUNT(y) FROM zyx where id=usr.id) as value_2 
FROM users AS usr

That's work correct, but i would like to sum value_1 and value_2.

When i do this

SELECT id,
(SELECT COUNT(x) FROM xyz where id=usr.id) as value_1, 
(SELECT COUNT(y) FROM zyx where id=usr.id) as value_2, 
(value_1+value_2) as my_sum_value
FROM users AS usr

i got information about "value_1" and "value_2" not found.

I figure out, that i can use

SELECT id, 
(SELECT COUNT(x) FROM xyz where id=usr.id) as value_1, 
(SELECT COUNT(y) FROM zyx where id=usr.id) as value_2, 
((SELECT COUNT(x) FROM xyz where id=usr.id) as value_1, 
(SEL开发者_运维问答ECT COUNT(y) FROM zyx where id=usr.id)+
(SELECT COUNT(y) FROM zyx where id=usr.id) as value_2) as my_sum_value
FROM users AS usr

But, i have to write "twice" all of my code. Why i can't use "alias" called "value_1" and "value_2" ? After query, this values are correct, how to access sum value?


try this

SELECT id,
    SUM(
    (SELECT COUNT(x) FROM xyz where id=usr.id), 
    (SELECT COUNT(y) FROM zyx where id=usr.id)
    ) as my_sum_value
FROM users AS usr

you can't use the variables because they're in a subquery...

0

精彩评论

暂无评论...
验证码 换一张
取 消