locked
Sum up with unique records RRS feed

  • Question

  • Hello all,

    I am executing the following query

    SELECT HSN, ProductName, UOM, sum(Quantity) as Quantity, sum(AmountPlusTax) as AmountPlusTax, sum(TaxableValue) as TaxableValue, sum(IGSTAmount) as IGSTAmount, sum(CGSTAmount) as CGSTAmount, sum(SGSTAmount) as SGSTAmount, '' as CessAmount FROM AccuSaleCustomer WHERE BillMode = 'Invoice' AND InvoiceDate >= '2017-10-01' AND InvoiceDate <= '2017-12-31' GROUP BY HSN, ProductName, UOM ORDER BY HSN ASC

    and getting the following result

    Now, please look the HSN Column, I would like to combine all the rows by unique HSN and sum up Quantity, AmountPlusTax, TaxableValue, IGSTAmount, CGSTAmount, SGSTAmount, CessAmount and unique HSN Record should display the ProductName as Product 3206, Product 3208, Product 3209 and so on.

    Can someone help ?

    Saturday, March 10, 2018 4:51 AM

Answers

All replies

  • Hi

    You are getting this result as you have put productName in select and group by clause and you have different product name for a single HSN code. To get HSN wise summmay you need to remove productName from your query.

    SELECT HSN, UOM, sum(Quantity) as Quantity, sum(AmountPlusTax) as AmountPlusTax, sum(TaxableValue) as TaxableValue, sum(IGSTAmount) as IGSTAmount, sum(CGSTAmount) as CGSTAmount, sum(SGSTAmount) as SGSTAmount, '' as CessAmount FROM AccuSaleCustomer WHERE BillMode = 'Invoice' AND InvoiceDate >= '2017-10-01' AND InvoiceDate <= '2017-12-31' GROUP BY HSN, UOM ORDER BY HSN ASC


    Ashish Pandey


    Saturday, March 10, 2018 6:30 AM
  • In addition to Ashish's answer, if you want to display the product name as the string "Product " plus the HSN value, just add that as an expression to your query...

    select
     HSN,
     ProductName = "Product " + cast(HSN as varchar(10)), -- or cast as char(4) if they're always 4 characters long
     ....
    from
     ....
    


    Saturday, March 10, 2018 6:51 AM
  • please give us your table data and what you want at result query 

    we don't interesting what you was write in code

    thats very simple for you and for us 

    Saturday, March 10, 2018 9:02 PM
  • Deleted
    Sunday, March 11, 2018 9:28 AM