CODEWITHSUNDEEP
mysqlresultset
KaeL
KaeL

Reputation: 3659

MySql Result Set Combine Columns

I have this result set from my query:

OrderId CustomerId  ProducerId  CustomerPayment ProducerPayment
1       1           3           10              5
1       1           4           10              5
1       2           3           10              5
1       2           4           10              5

I need to return this result into this:

OrderId UserId  Payment
1       1       20
1       2       20
1       3       10
1       4       10

Just combining the CustomerId and ProducerId into UserId. Same with the Payment Columns.

Is there any way to achieve this with using just a simple select and group by? I'm avoiding temp tables, calling multiple same queries and like for optimization. I hope this is possible.

Thanks a lot

Upvotes: 0

Views: 388

Answers (2)

Yoram de Langen
Yoram de Langen

Reputation: 5499

Try something like this:

select
    OrderId,
    CustomerId,
    sum(CustomerPayment) Payment,
    group_concat(OrderId separator ',') listOrders /* list all OrderID's from the user and separates these with a , */
from your_table
group by CustomerId

Dont know how you query looks like atm?

Upvotes: 0

Utku Yıldırım
Utku Yıldırım

Reputation: 2277

SELECT 
OrderId,
CustomerID AS UserId,
SUM (CustomerPayment) As Payment
FROM orders
UNION ALL
SELECT 
OrderId,
ProducerId AS UserId,
SUM (ProducerPayment) As Payment
FROM orders

Upvotes: 1

Related Questions