CODEWITHSUNDEEP
sqlsql-server
Xavier Maldonado
Xavier Maldonado

Reputation: 310

sql use aggregate function that counts a unique value with group by using inner joins

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;

Upvotes: 1

Views: 114

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19215

You case use a case statement inside a sum

SUM(CASE WHEN <xyz> THEN 1 ELSE 0 END)

But for the count of unique orders, use SELECT(DISTINCT ) on a key that is unique in the order table

SELECT COUNT(DISTINCT O.OrderID) As DistinctOrders FROM Table

Upvotes: 1

Related Questions