SQL Server Performance

2 sub-counts in same query possible?

Discussion in 'General Developer Questions' started by erkdahl, Apr 12, 2007.

  1. erkdahl New Member

    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!
  2. erkdahl New Member

    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

  3. khtan New Member

    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
  4. erkdahl New Member

    Thanks man!

Share This Page