I have this query, where I'm already using a ROW_NUMBER() function. My question is, is it possible to use it too, on cash, debit and credit? Because I'm getting duplicated data in every item row, I want to only get the first row of each payment type to pair them with the totalsale column.

My attempt:

SELECT 
    i.Name,
    i.Coditem,
    Pt.docnumber,
    pt.doctype,
    i.ItemPrice,
    SUM(CASE WHEN Pt.Paymentid = 01 THEN pt.Amount END) AS 'Cash',
    SUM(CASE WHEN Pt.Paymentid = 02 THEN pt.Amount END) AS 'Debit',
    SUM(CASE WHEN Pt.Paymentid = 03 THEN pt.Amount END) AS 'Credit',
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY i.doctype, i.docnumber ORDER BY i.coditem) = 1
            THEN s.totalsale 
    END AS TotalSale
FROM 
    PaymentsTable AS Pt
INNER JOIN
    ItemSales I ON i.docnumber = pt.docnumber
LEFT JOIN
    sales s ON (s.docnumber = i.docnumber)
GROUP BY 
    Pt.docnumber, pt.doctype, i.name, i.coditem, 
    i.itemprice, s.TotalSale, i.doctype, i.docnumber  

Here's the example database

Dbfiddle Test Db

CodePudding user response：

You can use CTE or derived table to simplify your query so that you don't need to perform GROUP BY on every column in your result

-- Tables & Sample data are from your db<>fiddle link. 
-- Reproduced here fore ease of reference
create table PaymentsTable (
PaymentId varchar(10),
DocNumber varchar(20),
DocType varchar(40),
Amount decimal(18,2));

create table ItemSales(
Name varchar(20),
DocType Varchar(20),
DocNumber varchar(20),
CodItem varchar(15),
ItemPrice decimal(18,2));

create table sales(
DocNumber varchar(20),
DocType varchar(20),
TotalSale decimal(18,2));

insert into paymentstable values (01,2020, 'receipt', 100);
insert into paymentstable values (02,2020, 'receipt',150);
insert into paymentstable values (03,2020, 'receipt',10);
insert into paymentstable values (01,220, 'invoice', 650);
insert into paymentstable values (02,220, 'invoice',500);
insert into paymentstable values (01,600, 'order', 1500);

insert into ItemSales values ('P1', 'Receipt', 2020, 010203, 100);
insert into ItemSales values ('P2', 'Receipt', 2020, 010204, 100);
insert into ItemSales values ('P3', 'Receipt', 2020, 010205, 60);
insert into ItemSales values ('Table', 'Invoice', 220, 'Tb05', 300);
insert into ItemSales values ('Bed', 'Invoice', 220, 'b01', 850);
insert into ItemSales values ('toaster', 'Order', 600, 'T01', 1500);

insert into sales values (2020, 'receipt', 260);
insert into sales values (220, 'invoice', 1150);
insert into sales values (600, 'order', 1500);

The modified query

SELECT I.Name
     , I.Coditem
     , S.DocNumber
     , Pt.doctype
     , I.ItemPrice
     , CASE WHEN I.ItemNo = 1 THEN Pt.Cash     END AS Cash
     , CASE WHEN I.ItemNo = 1 THEN Pt.Debit    END AS Debit
     , CASE WHEN I.ItemNo = 1 THEN Pt.Credit   END AS Credit
     , CASE WHEN I.ItemNo = 1 THEN S.TotalSale END AS TotalSale
FROM sales S
     INNER JOIN 
     (
         -- generating the row_number in sub-query, makes it easier
         -- for outer query to reference the ItemNo
         SELECT Name
              , DocType
              , DocNumber
              , CodItem
              , ItemPrice
              , row_number() OVER (PARTITION BY DocType, DocNumber 
                                       ORDER BY CodItem) AS ItemNo
         from   ItemSales 
     ) I                         ON I.DocNumber = S.DocNumber
     INNER JOIN 
     (
          -- pivoting query as what Dale show you in your last question
          SELECT Pt.DocNumber
               , Pt.DocType
               , SUM(CASE WHEN Pt.PaymentId = 01 THEN Pt.Amount END) AS 'Cash'
               , SUM(CASE WHEN Pt.PaymentId = 02 THEN Pt.Amount END) AS 'Debit'
               , SUM(CASE WHEN Pt.PaymentId = 03 THEN Pt.Amount END) AS 'Credit'
          FROM   PaymentsTable Pt
          GROUP BY Pt.docnumber, Pt.doctype
     ) Pt                         ON I.DocNumber = Pt.DocNumber

Side note: please be consistent with your table naming. You have table sales in lower case while the rest are in camel case. Also use the same case in your query as in your table DDL.

CodePudding user response：

If I understand the question correctly, a possible approach is a combination of PIVOT relational operator and DENSE_RANK() function:

SELECT 
   Name, CodItem, DocNumber, DocType, ItemPrice, 
   Cash = CASE WHEN rn = 1 THEN Cash END, 
   Debit = CASE WHEN rn = 1 THEN Debit END,
   Credit = CASE WHEN rn = 1 THEN Credit END, 
   TotalSale = CASE WHEN rn = 1 THEN TotalSale END 
FROM (
   SELECT 
      i.Name, i.CodItem, pt.DocNumber, pt.DocType, i.ItemPrice, pt.Amount, s.TotalSale,
      CASE
         WHEN pt.Paymentid = 01 THEN 'Cash'
         WHEN pt.Paymentid = 02 THEN 'Debit'
         WHEN pt.Paymentid = 03 THEN 'Credit'
         ELSE 'Other'
      END AS PaymentId,
      DENSE_RANK() OVER (PARTITION BY i.DocType, i.DocNumber ORDER BY i.CodItem) AS rn
   FROM PaymentsTable AS pt
   INNER JOIN ItemSales i on i.docnumber=pt.docnumber
   LEFT JOIN Sales s on (s.docnumber=i.docnumber)
) t
PIVOT (
   SUM(Amount) FOR PaymentId IN ([Cash], [Debit], [Credit])
) p
ORDER BY DocNumber, DocType

Result: