L
L
Leonid Gilkovich2015-08-05 11:25:34
MySQL
Leonid Gilkovich, 2015-08-05 11:25:34

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

3 answer(s)
D
Dmitry, 2015-08-05
@bromterier

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

or
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

V
V Sh., 2015-08-05
@JuniorNoobie

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

Maybe I messed up something in the syntax, but it seems that MS SQL has CASE :-)

D
Dmitry, 2015-08-05
@thewind

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 question

Ask a Question

731 491 924 answers to any question