Tukukkk

In this Database I want to count the number of orders that contain only products of a certain category.

I know how to count all orders that also contain items of a certain category, i.e. category 1:

SELECT Count(DISTINCT orderdetails.orderid) AS "AllCat1" 

FROM   orderdetails 

       INNER JOIN orders 

               ON orderdetails.orderid = orders.orderid 

                  AND orderdetails.productid IN (SELECT DISTINCT productid 

                                                 FROM   products 

                                                 WHERE  categoryid = 1) 

WHERE  orderdate BETWEEN "1996-12-01" AND "1996-12-31"; 

I am having trouble finding an elegant way to get all orders that contain only category 1 items. I tried selecting all OrderIDs and grouping them by OrderID AND CategoryID:

SELECT * 

FROM   orderdetails 

       INNER JOIN orders 

               ON orderdetails.orderid = orders.orderid 

                  AND orderdate BETWEEN "1996-12-01" AND "1996-12-31" 

       INNER JOIN products 

               ON orderdetails.productid = products.productid 

GROUP  BY orderdetails.orderid, 

          categoryid;

But I have no idea how to count all OrderIDs that contain category 1 items exclusively. Is my approach right? Or is there a better way to do it (Which I am sure there is)

You can do filtering using HAVING clause. We basically Count the order details rows where category is 1 for an order. It should be equal to the total count of rows for that order. This would ensure that all the categories in an order is 1 only.

SELECT od.orderid 

FROM   orderdetails AS od 

       INNER JOIN orders AS o

               ON od.orderid = o.orderid 

                  AND o.orderdate BETWEEN "1996-12-01" AND "1996-12-31" 

       INNER JOIN products AS p

               ON od.productid = p.productid 

GROUP  BY od.orderid 

HAVING COUNT(CASE WHEN p.categoryid = 1 THEN 1 END) = COUNT(*)

It is advisable to use Aliasing in case of multi-table queries for Code clarity and readability

You can use group by and having . . . but you need two levels. To get the orders that are all in one (or a set of categories) by doing:

SELECT o.orderId 

FROM orders o JOIN

     orderdetails od

     ON od.orderid = o.orderid JOIN

     products p

     ON p.productid = od.productid

WHERE o.orderdate BETWEEN '1996-12-01' AND '1996-12-31'

GROUP BY o.orderId

HAVING SUM(CASE WHEN p.categoryid IN (1) THEN 1 ELSE 0 END) = COUNT(*);

The count needs a subquery:

SELECT COUNT(*)

FROM (SELECT o.orderId 

      FROM orders o JOIN

           orderdetails od

           ON od.orderid = o.orderid JOIN

           products p

           ON p.productid = od.productid

      WHERE o.orderdate BETWEEN '1996-12-01' AND '1996-12-31'

      GROUP BY o.orderId

      HAVING SUM(CASE WHEN p.categoryid IN (1) THEN 1 ELSE 0 END) = COUNT(*)

     ) o;