I searched and found similar questions online but not my particular one, they all use where or having clause.If theres one similar to mine please link it. It's a 2 part question and I have the first one done. Thank you in advance.
Okay so heres the question, part 1

"Find by customer, the total cost and the total discounted cost for each product on the order ?".

It also asks to use inner joins to find the customer and order it a specific way. Below is the answer.

```
SELECT
C.companyname, O.orderid, O.orderdate, P.productname,
OD.orderid, OD.unitprice, OD.qty, OD.discount,
(OD.unitprice * OD.qty - (OD.qty * OD.discount)) AS TotalCost,
(OD.qty * OD.discount) AS TotalDiscountedCost
FROM
Sales.Customers AS C
INNER JOIN
Sales.Orders AS O ON C.custid = O.custid
INNER JOIN
Sales.OrderDetails OD ON O.orderid = OD.orderid
INNER JOIN
Production.Products as P ON OD.productid = P.productid
ORDER BY
C.companyname, O.orderdate;
```

Now the second question is to

follow up and resume the first one by "customer and the order date year, the total cost and the total discounted cost on the order ?". It also asks for this, "Project following columns in the select clause as.

```
GroupByColumns.companyname
GroupByColumns.OrderdateYear
AggregationColumns.CountNumberOfIndividualOrders
AggregationColumns.CountNumberOfProductsOrders
AggregationColumns.TotalCost
AggregationColumns.TotalDiscountedCost
```

Finally to order by company name and orderdateYear( which are groups). Where im stuck is how to count the specific orders of qty that equal 1 as an aggregate function in the SELECT clause. I know it has to use the aggregate function COUNT because of the GROUP BY, just don't know how to. This is what I have.

```
SELECT
C.companyname, YEAR(O.orderdate) AS orderyear,OD.qty,
-- Where in the count function or if theres another way do I count all the
--single orders
--COUNT(OD.qty) AS indiviualorders,
(OD.unitprice * OD.qty - (OD.qty * OD.discount)) AS TotalCost,
(OD.qty * OD.discount) AS TotalDiscountedCost
FROM
Sales.Customers AS C
INNER JOIN
Sales.Orders AS O ON C.custid = O.custid
INNER JOIN
Sales.OrderDetails OD ON O.orderid = OD.orderid
INNER JOIN
Production.Products as P ON OD.productid = P.productid
GROUP BY
C.companyname, YEAR(O.orderdate)
ORDER BY
C.companyname, O.orderdate;
```