CODEWITHSUNDEEP
sqlsql-server
Hammas
Hammas

Reputation: 1214

How to Group By in SQL Server Query

I'm using this query to get the Sum of SaleAmount for each type (SOType) of Sale Invoices.
I am getting the result but the result is not grouped by SOType. Have tried to use Group by Outside the query after where condition but getting an error as

"Column 'SaleInvoices.InvoiceID' is invalid because it is not contained in either aggregate or group by function". 

DECLARE @fromDate Datetime = '2019/05/23'
DECLARE @toDate Datetime = '2019/10/25'
DECLARE @isKpi int = '1'

SELECT (                      
  (Select Sum((Isnull(I.Quantity,0)*Isnull(I.SalePrice,0))+((Isnull(I.Quantity,0)*Isnull(I.SalePrice,0) - I.Discount) *(I.TAX/100))) 
  from ItemsSold as I 
  where I.InvoiceId= S.InvoiceID and I.InvoiceType='Sale Invoice'
  ) -
  (Select isnull(Sum((Isnull(I.Quantity,0)*Isnull(I.SalePrice,0))+((Isnull(I.Quantity,0)*Isnull(I.SalePrice,0) - I.Discount)*(I.TAX/100))),0)
  from ItemsSold as I 
  where I.InvoiceId= S.InvoiceID and I.InvoiceType='Sale Return'

  )) as Total

  ,S.SOType as SOType 

  FROM SaleInvoices AS S

  where S.OrderDate>=Convert(VARCHAR,@fromDate,111) and S.OrderDate<=Convert(varchar,@toDate,111)

enter image description here

Upvotes: 1

Views: 235

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You want conditional aggregation. The logic should look something like this:

select s.SOType,                      
       sum(case when i.invoicetype = 'Sale Invoice'
                then (I.Quantity * I.SalePrice) * (1 - i.discount) * i.tax / 100.0 
                when i.invoicetype = 'Sale Return'
                then - (I.Quantity * I.SalePrice) * (1 - i.discount) * i.tax / 100.0
            end) as Total
from SaleInvoices s join
     ItemsSold i
     on i.InvoiceId= s.InvoiceID 
where s.OrderDate >= @fromDate and 
      s.OrderDate <= @toDate
group by s.SOType ;

I'm not sure I got the arithmetic correct.

Notes:

  • The group by clause defines the rows being returned by the query. If you want one row per SOType then you want to GROUP BY SOType.
  • Use date comparisons and functions for dates. It is absurd to convert a date to a string to compare to a date.
  • You probably don't need COALESCE() or ISNULL() to handle NULL values. These are generally ignored by aggregation functions.

Upvotes: 1

Related Questions