0

enter image description here enter image description here Here's the Query

SELECT 
MAX (Supplier.SupplierName) as Supplier, 
MAX (Department.Name) as Department,
MAX (Category.Name) as Category,
MAX (ItemClass.ItemLookupCode) as Matrix,
MAX (Item.ItemLookupCode) as ItemLookupCode,
MAX (Item.Description) as Description,
SUM (TransactionEntry.Quantity) as QtySold,
MAX (Item.Cost) as Cost,
MAX (Item.Price) as Price,
MAX (TransactionEntry.Price) as SoldPrice,
SUM (TransactionEntry.Price * TransactionEntry.Quantity) as TotalSale,
MAX (Item.Quantity) as OnHand

  FROM        TransactionEntry 
  INNER JOIN  [Transaction] WITH(NOLOCK) 
              ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionENtry.ItemType <> 9 AND TransactionEntry.StoreID = [Transaction].StoreID 
  INNER JOIN  Batch WITH(NOLOCK) 
              ON [Transaction].BatchNumber = Batch.BatchNumber AND [Transaction].StoreID = Batch.StoreID
  LEFT JOIN   Item WITH(NOLOCK) 
              ON TransactionEntry.ItemID = Item.ID 
  LEFT JOIN   Department WITH(NOLOCK) 
              ON Item.DepartmentID = Department.ID 
  LEFT JOIN   Category WITH(NOLOCK) 
              ON Item.CategoryID = Category.ID 
  LEFT JOIN   Supplier WITH(NOLOCK) 
              ON Item.SupplierID = Supplier.ID 
  LEFT JOIN   ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) 
              ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID 
  LEFT JOIN   ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) 
              ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID
  LEFT JOIN   ReasonCode AS ReasonCodeReturn WITH(NOLOCK) 
              ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID
  LEFT JOIN   Store ON [Transaction].StoreID = Store.ID
  LEFT JOIN   ItemClassComponent WITH(NOLOCK) 
      ON Item.ID = ItemClassComponent.ItemID
  LEFT JOIN   ItemClass WITH(NOLOCK) 
      ON ItemClassComponent.ItemClassID = ItemClass.ID

WHERE    DATEDIFF(week, [Transaction].Time, GETDATE()) = 1 AND
Supplier.SupplierName = 'Name'

GROUP BY Item.ItemLookupCode

Here's the query and how do I add summary row for total numbers for some of the column? I tried several things but could not find anything...

Please help!!!!

http://i29.photobucket.com/albums/c259/xkrntamax/Capture_zps511d8kun.jpg

D.RYU
  • 23
  • 6
  • Would you please provide some sample table data? – Ullas Mar 15 '16 at 13:32
  • You should read this before you continue littering your database with NOLOCK hints...unless you are ok with results that are mostly accurate most of the time. Since this looks like a financial application I doubt that missing and/or duplicate rows is acceptable. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Mar 15 '16 at 13:34
  • Do I need to add row query to calculate the total? – D.RYU Mar 15 '16 at 14:31

1 Answers1

2

You are probably looking for grouping sets or with rollup:

Change the group by clause to:

GROUP BY GROUPING SETS ((Item.ItemLookupCode), ())
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I get the same result. Do I need to add query to calculate total row? – D.RYU Mar 15 '16 at 14:22
  • When you run this query, it should add an extra row which is the grouping conditions applied to all the data. That is what the `()` does with `GROUPING SETS`. The result should not be the same. – Gordon Linoff Mar 15 '16 at 21:34
  • Thank you! It works but is there any way to show just the column that needs to be sum? – D.RYU Mar 16 '16 at 14:10
  • @user3524687 . . . All the rows in a result set have the same columns, so I'm not sure what you are asking. – Gordon Linoff Mar 16 '16 at 23:59
  • Hi Gordon, I have attached the screenshot. first screenshot, last row only have sum of QtySold, TotalSale, and OnHand(somehow this will not sum). That's how I like to show on the report. If you look at the second screenshot, last column has all information. Are there any way to fix?? – D.RYU Mar 17 '16 at 15:03