Given a date, the table displays the items sold on that date.

The table groups the category of the items and show the total sales value for each category. At the end, the report shows the total sales value for the day(s). Something like this:

```
ID    Category  Price   Units   Total Value
----------------------------------------------------
2244    class   10.50    10       105.00   
2555    class   5.00      5       25.00   
3455    class   20.00     1       20.00   

  Total                  16       150.00    

1255    pop     20.00     5       100.00
5666    pop     10.00    10       100.00

  Total                  15       200,00

1244    rock    2.50     20       50.00   
8844    rock    5.00     50       250.00   

  Total                  70       300.00

----------------------------------------------

Total Daily     Sales   101       650.00

```

DBMS: SQL Server 2012

Bolded: primary keys

Item (**upc**, title\*, type, category, company, year, price, stock)  

PurchaseItem (**receiptId**, **upc**, quantity)  

Order (**receiptId**, date, cid, card#, expiryDate, expectedDate, deliveredDate) 

```
Rough work of what I have so far..

SELECT I.upc, I.category, I.price, P.quantity, P.quantity*I.price AS totalValue, SUM(totalValue), SUM(P.quantity) AS totalUnits, O.date
FROM Item I, Order O
JOIN (SELECT P.quantity
      FROM PurchaseItem P, Item I
      WHERE I.upc = P.upc)
ON I.upc = P.upc
WHERE O.date = ({$date}) AND O.receiptId = P.receiptId
GROUP BY I.upc, I.category, I.price, P.quantity, totalValue, O.date

```

Alright, this isn't right and I'm kind of stuck. Need some help!  

I want it so it produces the total value of items from one category then in the end, it will add up the total value of the items from all categories.

***SAMPLE TABLES***  

Item(2568, Beatles, rock, Music Inc, 1998, 50.50, 5000)  

PurchaseItem (5300, 2568, 2)  

Order (5300, 10/09/2014, ...Not important..) cid is customerId and card# is credit card number.