CODEWITHSUNDEEP
sqlsql-server-2008
Orhan Cinar
Orhan Cinar

Reputation: 8410

Calculating Subtotals

How can i sum values in Result 1 like Result 2

Result 1
    SalesRep    Customer    Product Quantity    Total
    Doe, John   AA Corp     P1      50          5000
    Doe, John   CA Corp     P2      67          6030
    Doe, John   EA Corp     P3      46          5980
    Doe, John   GA Corp     P2      22          1980
    Doe, John   HA Corp     P3      43          5590
    Doe, John   IA Corp     P1      35          3500 | Sum this two 
    Doe, John   IA Corp     P2      24          2160 | make second record 0
    Doe, John   JA Corp     P2      66          5940
    Doe, John   MA Corp     P4      59          7670 
    Doe, John   OA Corp     P2      43          3870 | Sum this two 
    Doe, John   OA Corp     P4      14          1820 | make second record 0
    Doe, John   PA Corp     P1      89          8900


Result 2
    SalesRep    Customer    Product Quantity    TotalPrice
    Doe, John   AA Corp     P1      50          5000
    Doe, John   CA Corp     P2      67          6030
    Doe, John   EA Corp     P3      46          5980
    Doe, John   GA Corp     P2      22          1980
    Doe, John   HA Corp     P3      43          5590
    Doe, John   IA Corp     P1      59          5660
    Doe, John   IA Corp     P2      0           0
    Doe, John   JA Corp     P2      66          5940
    Doe, John   MA Corp     P4      59          7670
    Doe, John   OA Corp     P2      57          5690
    Doe, John   OA Corp     P4      0           0
    Doe, John   PA Corp     P1      89          8900

Upvotes: 1

Views: 244

Answers (2)

user554546
user554546

Reputation:

Well, if you reeeeeeally need the rows with zeroes, then you can do this:

select salesrep,customer,min(product),sum(quantity) as quantity,sum(price) as totalprice
from results1
group by salesrep,customer
union
select salesrep,customer,max(product),0 as quantity,0 as totalprice
from results1
group by salesrep,customer
having count(1)>1;

That technically doesn't take into account what would happen if there were more than two (salesrep,customer,product) triples, but that can be fixed as long as MS-SQL has equivalents to the generate_series() and rank() functions of PostgreSQL.

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220797

One possibility is this, although I really don't see the business case. I guess it's just a technical question:

SELECT 
  T.SalesRep, T.Customer, T.Product,
  CASE WHEN EXISTS (SELECT 1 FROM MyTable AS T1
                    WHERE T1.SalesRep = T.SalesRep
                    AND T1.Customer = T.Customer
                    AND T1.Product < T.Product)
       THEN 0
       ELSE SUM(T.Quantity) OVER (PARTITION BY T.SalesRep, T.Customer)
  END AS Quantity,
  CASE WHEN EXISTS (SELECT 1 FROM MyTable AS T1
                    WHERE T1.SalesRep = T.SalesRep
                    AND T1.Customer = T.Customer
                    AND T1.Product < T.Product)
       THEN 0
       ELSE SUM(T.Total) OVER (PARTITION BY T.SalesRep, T.Customer)
  END AS Total
FROM MyTable AS T

For the two CASE clauses, they read: "when there is a record for the same sales representative and customer with a lesser product (that's my assumption?), then make the value zero. Otherwise, sum up all values grouped by sales representative and customer. This will work also with more than two (distinct!) products per sales rep. and customer.

Note: If there can be several identical products per sales rep. and customer, this won't work, and you'd have to compare some other values (e.g. T1.ID < T.ID) in the two nested selects

Note also: this could be wrong, as I had to make some assumptions regarding your requirements.

Upvotes: 3

Related Questions