2 sub-counts in same query possible?

Hi guys!

Lets say you have three tables.
CUSTOMER(CUST_ID (PK), NAME)
ORDER(CUST_ID (PKFK), SEQ_NR (PK), DATE)
ORDERLINE(CUST_ID(PKFK), SEQ_NR (PKFK), ITEM_ID (PKFK))

ITEM_ID references to a item table.

Now you want a query to list all orders for a spesific customer, the result:
CUST_ID, NAME, SEQ_NR, DATE, TOTAL_NUMBER_OF_ITEMS_IN_ALL_ORDERS, TOTAL_NUMBER_OF_ITEMS_IN_THIS_ORDER
Number of rows should equal number of orders for the spesific customer.

Result something like this:
1, 'Customer1', 1, '1980-01-01', 10 , 2
1, 'Customer1', 2, '1981-01-01', 10 , 1
1, 'Customer1', 3, '1982-01-01', 10 , 3
1, 'Customer1', 4, '1983-01-01', 10 , 2
1, 'Customer1', 5, '1984-01-01', 10 , 2

Never mind the odd primary keys or why a need that spesific query which doubles up some results.

Is this possible in one single query?

Thanks in advance for your helpful answers!

Ok, i found a solution so never mind unless you think my way is a slow or bad way. I had to use a subquery for one count, but i guess there is now way around?



SELECT
C.CUST_ID,
C.NAME,
O.SEQ_NR,
O.DATE,
(SELECT COUNT(OL1.ITEM_ID) FROM ORDERLINE OL1 WHERE OL1.CUST_ID = C.CUST_ID) AS TOTAL_NUMBER_OF_ITEMS_IN_ALL_ORDERS,
COUNT(OL.ITEM_ID) AS TOTAL_NUMBER_OF_ITEMS_IN_THIS_ORDER
FROM
CUSTOMER C
LEFT JOIN
ORDER O
ON O.CUST_ID = C.CUST_ID
LEFT JOIN
ORDERLINE OL
ON OL.CUST_ID = O.CUST_ID
AND OL.SEQ_NR = O.SEQ_NR
WHERE
O.CUST_ID = #VALUE#
GROUP BY
C.CUST_ID,
C.NAME,
O.SEQ_NR,
O.DATE

This is gives better performance


SELECT
C.CUST_ID,
C.NAME,
O.SEQ_NR,
O.DATE,
T.TOTAL_NUMBER_OF_ITEMS_IN_ALL_ORDERS,
COUNT(OL.ITEM_ID) AS TOTAL_NUMBER_OF_ITEMS_IN_THIS_ORDER
FROM
CUSTOMER C
LEFT JOIN
(
SELECT CUST_ID, COUNT(ITEM_ID) AS TOTAL_NUMBER_OF_ITEMS_IN_ALL_ORDERS
FROM ORDERLINE
GROUP BY CUST_ID
) T
ON T.CUST_ID = C.CUST_ID
LEFT JOIN
ORDER O
ON O.CUST_ID = C.CUST_ID
LEFT JOIN
ORDERLINE OL
ON OL.CUST_ID = O.CUST_ID
AND OL.SEQ_NR = O.SEQ_NR
WHERE
O.CUST_ID = #VALUE#
GROUP BY
C.CUST_ID,
C.NAME,
O.SEQ_NR,
O.DATE



KH

Thanks man!