(
SELECT
SUM(`itogo`) AS rr, `Id_klient`, klient.name
FROM
`bill_n`, klient
WHERE
(klient.id = bill_n.Id_klient) AND (bill_n.`vid` = 0) AND (bill_n.`status` = 1)
)
UNION
(
SELECT
SUM(`itogo`) AS rr1, `Id_klient`, klient.name
FROM
`bill_n`, klient
WHERE
(klient.id = bill_n.Id_klient) AND (bill_n.`vid` = 1) AND (bill_n.`status` = 1)
)
how do I make it so there's a fourth column rr1
The question isn't very clear.
I think what was meant is this:
(select sum(`itogo`) AS rr, `Id_klient`, klient.name, null as rr1 from … )
union
(select null as rr, `Id_klient`, klient.name, SUM(`itogo`) AS rr1 from …)
In other words, add this column to both parts, but depending on the situation put null there right away
yes that's exactly what I mean, thanks, but now how do I group by groups GROUP BY `Id_klient`?
here's what I'm doing
(
SELECT
SUM(`itogo`) AS rr, `Id_klient`, klient.name, null as rr1
FROM
`bill_n`, klient
WHERE
(klient.id = bill_n.Id_klient) AND (bill_n.`vid` = 1) AND (bill_n.`status` = 1)
GROUP BY
`Id_klient`
)
UNION ALL
(
SELECT
null as rr, `Id_klient`, klient.name, SUM(`itogo`) AS rr1
FROM
`bill_n`, klient
WHERE
(klient.id = bill_n.Id_klient) AND (bill_n.`vid` = 0) AND (bill_n.`status` = 1)
GROUP BY
`Id_klient`
)
and the result
=============================================================================================================================================================================================================
| rr | Id_klient | name | rr1 |
=============================================================================================================================================================================================================
| 0,07 | 15 | New client 15------ | null |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| null | 15 | New client 15------ | 200,04 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| null | 17 | New 17client | 9,12 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How are the orders and klient tables related? Does each record in klient have exactly one record in orders? Does each record in klient have at most one record in orders? Can each record in klient have an arbitrary number of records in orders?
For each record in klient there can be an arbitrary number of records in orders, but orders has a field id.klient and klient has a field id, and they're logically related, sort of
Then which orders.summa field do you want to select if, for a given Id_klient, there can be several rows with potentially different values of the orders.summa field?
select
sum(if(bill_n.`vid` = 1,`itogo`,0)) as rr,
sum(if(bill_n.`vid` = 0,`itogo`,0)) as rr1,
`Id_klient`,
klient.name
from
`bill_n` join
klient on klient.id = bill_n.Id_klient
where
bill_n.`status` = 1
group by
`Id_klient`
Yes, I figured it out now, thanks so much everyone!!!!!!!!!!!!!
SELECT bill_n.`Id_klient`, klient.name,
SUM(IF(bill_n.`vid` = 0, `itogo`, 0)) AS rashod,
SUM(IF(bill_n.`vid` = 1, `itogo`, 0)) AS prihod,
SUM(IF(orders.Schet = 0, orders.summa, 0)) AS za_RN,
SUM(IF(orders.Schet = 1, orders.summa, 0)) AS za_PR
FROM
`bill_n`
JOIN
klient ON klient.id = bill_n.Id_klient
JOIN
orders ON orders.Id_klient = klient.id
WHERE
bill_n.`status` = 1
GROUP BY
`Id_klient`
But now there's a different problem [size=18]mysql recalculates the join an extra time[/size]
SELECT
bill_n.`Id_klient`, klient.name, ROUND(SUM(IF(bill_n.`vid` = 0, bill_n.`itogo`, 0)), 2) AS rashod,
ROUND(SUM(IF(orders.Schet = 0, orders.summa, 0)), 2) AS za_RN
FROM
`bill_n`
JOIN klient ON klient.id = bill_n.Id_klient
JOIN orders ON orders.Id_klient = klient.id
WHERE
bill_n.`status` = 1
GROUP BY
bill_n.`Id_klient`;
gives the result
=============================================
|Id_klient | name | rashod | za_RN |
=============================================
| 15 | New | 200,04 | 0,0 |
| | client | | |
| | 15------ | | |
---------------------------------------------
| 17 | New | 72,96 | 20,12 |
| | 17client | | |
---------------------------------------------
but in reality
=============================================
|Id_klient | name | rashod | za_RN |
=============================================
| 15 | New | 200,04 | 0,0 |
| | client | | |
| | 15------ | | |
---------------------------------------------
| 17 | New | 9,12 | 20,12 |
| | 17client | | |
---------------------------------------------
apparently, because of the nesting it recalculates an extra time — how do I do this correctly?????? and is that even possible?
indeed, in orders there are 8 records with orders.Id_klient = klient.id=17
select
klient.id,
klient.name,
bill_n_group.rashod,
orders_group.za_RN
from
klient left join
(
select
Id_klient,
ROUND(SUM(IF(`vid` = 0, `itogo`, 0)), 2) AS rashod
from
bill_n
group by
Id_klient
) as bill_n_group on klient.id=bill_n_group.Id_klient left join
(
select
Id_klient,
ROUND(SUM(IF(Schet = 0, summa, 0)), 2) AS za_RN
from
orders
group by
Id_klient
) as orders_group on klient.id=orders_group.Id_klient
Comments