CODEWITHSUNDEEP
mysql
palPalani
palPalani

Reputation: 105

MySql get multiple rows in single row

I have the following Mysql procedure.

select

case temp.lineItemTypeID when 1 then temp.tt else 0 end case as planTotalAmount,
case temp.lineItemTypeID when 2 then temp.tt else 0 end case as discountTotalAmount

from (

SELECT
    lineItemTypeID,
    sum(lineitemAmount) as tt from tbl_invoicelineitem
    where date(lineItemcreatedDate) IS NOT NULL
    group by lineItemTypeID
) temp;

I want to results in single which is returns by sub query. Ex:

planTotalAmount  |  discountTotalAmount
10                | 2                   `

but I am getting

planTotalAmount  |  discountTotalAmount
10                | 0                   
0                 | 2                   `

How to fix that?

Upvotes: 0

Views: 44

Answers (1)

Barranka
Barranka

Reputation: 21047

Aggregate the case...end expressions:

select
    sum(case temp.lineItemTypeID when 1 then temp.tt else 0 end case) as planTotalAmount,
    sum(case temp.lineItemTypeID when 2 then temp.tt else 0 end case) as discountTotalAmount
from (
        SELECT
            lineItemTypeID,
            sum(lineitemAmount) as tt 
        from tbl_invoicelineitem
        where date(lineItemcreatedDate) IS NOT NULL
        group by lineItemTypeID
    ) temp;

Upvotes: 1

Related Questions