Answer the question
In order to leave comments, you need to log in
How to calculate the values of two parameters in one query?
Hello!
There is, for example, such a table:
id | name | money | paymentType |
-----------------------------------------
1 | Ivan | 1000 | cash |
-----------------------------------------
2 | Ivan | 3000 | credit |
-----------------------------------------
3 | Ivan | 2300 | cash |
Is it possible to write such a query that would calculate the amount at once depending on the types of payment and display the following:
Ivan cash -> 3300 credit-> 3000
My query so far looks like this, but it displays the count for each type in a separate line naturally):
(select name, sum(money), from exmp where paymentType = 'Cash' group by name)
UNION
(select name, sum(money), from exmp where paymentType = 'Credit' group by name)
Answer the question
In order to leave comments, you need to log in
requests not checked
select exmp.name, sum(exmp.money), sum(exmp_t.money) from exmp
left join exmp as exmp_t on exmp_t.name = exmp.name
where exmp.paymentType = 'Cash' AND exmp_t.paymentType = 'Credit' group by exmp.name
select name, sum(money) as cash, (select sum(exmp_t.money) from exmp as exmp_t where exmp_t.paymentType = 'Credit' AND exmp_t.name=exmp.name) as credit from exmp where paymentType = 'Cash' group by name
SELECT t.Name,
SUM(CASE WHEN t.PaymentType='cash' THEN t.Money ELSE 0 END) AS Cash,
SUM(CASE WHEN t.PaymentType='credit' THEN t.Money ELSE 0 END) AS Credit
FROM table t
GROUP BY t.Name
Sum() + group by paymentType
If you need to do without other languages, then you can do this. Although the subselect option is slow.
SELECT t.`name`, SUM(t.`money`) as `cash`, (SELECT SUM(`money`) FROM `table` WHERE `paymentType`='credit' AND `name`=t.`name`) as `credit` FROM `table` t WHERE t.`paymenType`='cash' GROUP BY t.`name`
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question