CODEWITHSUNDEEP
sqlsql-servert-sql
louise
louise

Reputation: 23

Combining result rows

I'm trying to get the result below to be displayed group by Article. Would this be possible?
So that in the end I will only have 2 lines. The data from TYPE I & W will be combined.

The image to my query as below
image http://tikistudio.sg/F002.jpg

The query as below

SELECT INTSALEID as RECEIPT,
    STRSALETYPE as TYPE,
    STRORDERCODE as ARTICLE,
    max(case when strsaletype='I' then DBLQTYSOLD else null end) as [QTY SOLD],
    max(case when strsaletype='I' then CURSELLPRICE1 else null end) as [UNIT PRICE],
    max(case when strsaletype='W' then CUROFFERDISCOUNT else null end) as [DISCOUNT],
    max(case when strsaletype='I' then CURFOREIGNAMT else null end) as [GROSS AMOUNT]
FROM DAILYSALES 
WHERE STRTRADECODE='MM01' 
  and DTMTRADEDATE='01-01-2015' and INTSALEID='31086'
  and STRSALETYPE in ('I','W')
GROUP BY STRORDERCODE,STRSALETYPE,INTSALEID,DBLQTYSOLD,CURSELLPRICE1,
    CURFOREIGNAMT,CURDISCOUNT,CUROFFERDISCOUNT

Upvotes: 0

Views: 59

Answers (2)

Ewan
Ewan

Reputation: 1087

If you aggregate your data one more time grouping by Receipt, and Article, taking the max value for QTY Sold, Unit Price, Gross Amount and Min Value for Discount.

Select RECEIPT, ARTICLE, MAX([QTY SOLD]) as [QTY SOLD], MAX([UNIT PRICE]) as [UNIT PRICE], MIN([DISCOUNT]) as [DISCOUNT], MAX([GROSS AMOUNT]) as [GROSS AMOUNT]
From
(select INTSALEID as RECEIPT,
STRSALETYPE as TYPE,
STRORDERCODE as ARTICLE,
max(case when strsaletype='I' then DBLQTYSOLD else null end) as [QTY SOLD],
max(case when strsaletype='I' then CURSELLPRICE1 else null end) as [UNIT PRICE],
max(case when strsaletype='W' then CUROFFERDISCOUNT else null end) as [DISCOUNT],
max(case when strsaletype='I' then CURFOREIGNAMT else null end) as [GROSS AMOUNT]
from DAILYSALES 
where STRTRADECODE='MM01' 
and DTMTRADEDATE='01-01-2015' and INTSALEID='31086'
and STRSALETYPE in ('I','W')
group by STRORDERCODE,STRSALETYPE,INTSALEID,DBLQTYSOLD,CURSELLPRICE1,
CURFOREIGNAMT,CURDISCOUNT,CUROFFERDISCOUNT) x
Group by RECEIPT, ARTICLE

Upvotes: 0

Palanikumar
Palanikumar

Reputation: 7150

You have to remove some columns in Group By

SELECT 
   INTSALEID as RECEIPT,
   STRORDERCODE as ARTICLE,
   MAX(CASE WHEN strsaletype='I' THEN DBLQTYSOLD ELSE NULL END) as [QTY SOLD],
   MAX(CASE WHEN strsaletype='I' THEN CURSELLPRICE1 ELSE NULL END) as [UNIT PRICE],
   MAX(CASE WHEN strsaletype='W' THEN CUROFFERDISCOUNT ELSE NULL END) as [DISCOUNT],
   MAX(CASE WHEN strsaletype='I' THEN CURFOREIGNAMT ELSE NULL END) as [GROSS AMOUNT]
FROM DAILYSALES 
WHERE 
STRTRADECODE='MM01' 
and DTMTRADEDATE='01-01-2015' and INTSALEID='31086'
and STRSALETYPE in ('I','W')
GROUP BY STRORDERCODE,INTSALEID

Upvotes: 1

Related Questions