Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Creating Basic SQL Reports in SQL Server

Introduction
Custom classifications of business objects
Custom grouping of business objects
Custom counting of business objects
14. Summing of business values
Summary

Instruction

Perfect! Suppose we now want to show the total amount paid for each order alongside the amount paid for non-vegetarian products. Take a look:

SELECT
  O.OrderID,
  SUM(Quantity * OI.UnitPrice) AS TotalPrice,
  SUM(CASE
    WHEN P.CategoryID in (6, 8) THEN Quantity * OI.UnitPrice
    ELSE 0
  END) AS NonVegetarianPrice
FROM Orders O
JOIN OrderItems OI
  ON O.OrderID = OI.OrderID
JOIN Products P
  ON P.ProductID = OI.ProductID
GROUP BY O.OrderID;

So far, we wrote the queries with SUM(CASE WHEN...) in such a way that they could all be replaced with equivalent COUNT(CASE WHEN...) constructions. Here, however, SUM(CASE WHEN...) is the only option – we want to sum certain values (OI.Quantity * OI.UnitPrice) instead of merely counting rows.

Exercise

This time, we want a report that will show each supplier alongside their number of units in stock and their number of expensive units in stock. Show four columns: SupplierID, CompanyName, AllUnits (all units in stock supplied by that supplier), and ExpensiveUnits (units in stock with a unit price over 40.0, supplied by that supplier).

Stuck? Here's a hint!

JOIN the Products and Suppliers tables. Group by SupplierID and CompanyName.