Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Unanswered: TSQL COUNT WITH GROUP BY!! Help Needed!

    Hi guys,

    I have got a Test DB with the following tables:

    Customers and Orders (Sql below)

    What i need is to know how many orders in total the each Customer has ordered after a certain date, which is simple using the query below,

    SELECT C.CUSTOMER_NAME, (COUNT(O.ORDER_ID)) AS TOTAL_ORDERS FROM CUSTOMERS C left JOIN ORDERS O ON O.CUSTOMER_ID = C.CUSTOMER_ID
    WHERE O.ORDER_DATE >= GETDATE() - 1
    GROUP BY C.CUSTOMER_NAME
    However, i also want to include the customers that have not got any orders and they should come as 0. When i run the query above i get

    CUSTOMER_NAME TOTAL_ORDERS
    -------------------------------------------------- ------------
    DEF 3
    GHI 2

    But i want to include the Customers that have not placed an order at all as well as thos who have not placed an order after that date and it should come as 0. for e.g.

    CUSTOMER_NAME TOTAL_ORDERS
    -------------------------------------------------- ------------
    ABC 0
    DEF 3
    GHI 2
    JKL 0

    And i cant seem to be able to get that, when i add a WHERE clause, without the WHERE clause it works fine only for customers that have not placed any order (coz of the left join) it comes as 0 but with the WHERE clause i cant get the result and I need the WHERE clause somehow to get order total after a certain date?

    Any ideas, Please? Any help appreciated.

    Thanks

    USE [TEST]
    GO
    /****** Object: Table [dbo].[CUSTOMERS] Script Date: 12/27/2008 14:47:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[CUSTOMERS](
    [CUSTOMER_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [CUSTOMER_NAME] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
    CONSTRAINT [PK_CUSTOMERS] PRIMARY KEY CLUSTERED
    (
    [CUSTOMER_ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    USE [TEST]
    GO
    /****** Object: Table [dbo].[ORDERS] Script Date: 12/27/2008 15:27:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[ORDERS](
    [ORDER_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [ORDER_NUM] [int] NOT NULL,
    [CUSTOMER_ID] [numeric](18, 0) NOT NULL,
    [ORDER_DATE] [datetime] NOT NULL,
    CONSTRAINT [PK_ORDERS] PRIMARY KEY CLUSTERED
    (
    [ORDER_ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]


    INSERT INTO CUSTOMERS
    SELECT 'ABC'
    UNION ALL
    SELECT 'DEF'
    UNION ALL
    SELECT 'GHI'
    UNION ALL
    SELECT 'JKL'

    INSERT INTO ORDERS
    SELECT 123, 1, GETDATE() -1
    UNION ALL
    SELECT 231, 1, GETDATE() -1
    UNION ALL
    SELECT 432, 1, GETDATE() -1
    UNION ALL
    SELECT 431, 1, GETDATE() -1
    UNION ALL
    SELECT 121, 2, GETDATE()
    UNION ALL
    SELECT 453, 2, GETDATE()
    UNION ALL
    SELECT 545, 2, GETDATE()
    UNION ALL
    SELECT 566, 3, GETDATE()
    UNION ALL
    SELECT 654, 3, GETDATE()

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem is your WHERE condition operating on a column of the right table in a LEFT OUTER JOIN

    change the WHERE condition to an ON condition and bob's your uncle

    change this --

    SELECT C.CUSTOMER_NAME, (COUNT(O.ORDER_ID)) AS TOTAL_ORDERS
    FROM CUSTOMERS C left JOIN ORDERS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
    WHERE O.ORDER_DATE >= GETDATE() - 1
    GROUP BY C.CUSTOMER_NAME

    to this --

    SELECT C.CUSTOMER_NAME, (COUNT(O.ORDER_ID)) AS TOTAL_ORDERS
    FROM CUSTOMERS C left JOIN ORDERS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
    AND O.ORDER_DATE >= GETDATE() - 1

    GROUP BY C.CUSTOMER_NAME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Posts
    2

    Thumbs up

    Quote Originally Posted by r937
    the problem is your WHERE condition operating on a column of the right table in a LEFT OUTER JOIN

    change the WHERE condition to an ON condition and bob's your uncle

    change this --

    SELECT C.CUSTOMER_NAME, (COUNT(O.ORDER_ID)) AS TOTAL_ORDERS
    FROM CUSTOMERS C left JOIN ORDERS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
    WHERE O.ORDER_DATE >= GETDATE() - 1
    GROUP BY C.CUSTOMER_NAME

    to this --

    SELECT C.CUSTOMER_NAME, (COUNT(O.ORDER_ID)) AS TOTAL_ORDERS
    FROM CUSTOMERS C left JOIN ORDERS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
    AND O.ORDER_DATE >= GETDATE() - 1

    GROUP BY C.CUSTOMER_NAME

    That is f***ing awesome mate, so simple lol... Nice one dude u saved me hours of pondering.

    Thanks alot

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it makes sense when you think about it

    in a LEFT OUTER JOIN, if you get an unmatched row, then all the columns in the results row that came from the right table are NULL (that's how LEFT OUTER JOIN works, eh)

    if you then turn around and require that a column from the right table has to be equal to something, or greater than something, then you will effectively eliminate the unmatched rows, because NULL is not equal to anything, and NULL is not greater than anything

    by putting the condition into the ON clause, you are saying "find all rows in the right table which match on ID and also have a date greater than..."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I would question the table design. It apparently allows duplicate customers without any attribute to distinguish them other than an IDENTITY column. If CUSTOMER_ID is a surrogate that isn't exposed to business users then how are they supposed to distinguish one customer from another? And if the business key should really be customer_name then it would be better to declare that UNIQUE so that you could just do:

    SELECT CUSTOMER_NAME,
    (SELECT COUNT(*)
    FROM ORDERS
    WHERE ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID) TOTAL_ORDERS
    FROM CUSTOMERS;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •