Using Count to compile for each unique CustomerID

  • I want to create a column whereas I will count each instance a customerid has an orderid AND does not have an [ordershipped] . Below is sample DDL
    TABLE dbo.orders --o
    (
        OrderID (pk, int, NOT NULL),
        CustomerID varchar(5) NOT NULL,
        Company varchar(50) null,
        ordershipped datetime NULL,
        orderarrived datetime NULL
        CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
    );

    TABLE dbo.customers --cus
    (
        customerID (pk,varchar(5), NOT NULL),
        firstname nvarchar(50) null,
        lastname nvarchar(50) not null
        CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (customerID ASC)
    );
    TABLE dbo.orderdetails --ode
    (
        odeID smallint NOT NULL,
        OrderID (pk, int, NOT NULL),
        datebegin datetime NULL
        CONSTRAINT PK_odeID PRIMARY KEY CLUSTERED (odeID ASC)
    );

    Will want to count the amount of orders per customerid (they can have more than one orderid for each customerid)

    SELECT cu.customerid
    ,cu.company
    ,[still pending] = (select count(OrderID) from

    orders o where cu.customerid = o.orderid and o.ordershipped = '')
    FROM customers cu
    LEFT JOIN orders o on cu.customerid = o.customerid
    JOIN orderdetails ode on o.orderid = ode.orderid

    GROUP BY cu.customerid, cu.company

    ... but I need to make sure each unique customerid appears on each row, with the number of [still pending] for each customerid
    ???
    Thanks

  • Hello Zososql,

    your example is really hard to understand.
    It's good that you provide DDL and sample code. But please post DDL that works, and SQL-Statements that work too. Your Select contains a column that's not in the table.

    Also, please provide some sample data, and include your expected output too.
    Then, I guess, you will get an answer quite quickly here 🙂

  • Zososql - Wednesday, February 8, 2017 5:32 PM

    I want to create a column whereas I will count each instance a customerid has an orderid AND does not have an [ordershipped] . Below is sample DDL
    TABLE dbo.orders --o
    (
        OrderID (pk, int, NOT NULL),
        CustomerID varchar(5) NOT NULL,
        Company varchar(50) null,
        ordershipped datetime NULL,
        orderarrived datetime NULL
        CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
    );

    TABLE dbo.customers --cus
    (
        customerID (pk,varchar(5), NOT NULL),
        firstname nvarchar(50) null,
        lastname nvarchar(50) not null
        CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (customerID ASC)
    );
    TABLE dbo.orderdetails --ode
    (
        odeID smallint NOT NULL,
        OrderID (pk, int, NOT NULL),
        datebegin datetime NULL
        CONSTRAINT PK_odeID PRIMARY KEY CLUSTERED (odeID ASC)
    );

    Will want to count the amount of orders per customerid (they can have more than one orderid for each customerid)

    SELECT cu.customerid
    ,cu.company
    ,[still pending] = (select count(OrderID) from

    orders o where cu.customerid = o.orderid and o.ordershipped = '')
    FROM customers cu
    LEFT JOIN orders o on cu.customerid = o.customerid
    JOIN orderdetails ode on o.orderid = ode.orderid

    GROUP BY cu.customerid, cu.company

    ... but I need to make sure each unique customerid appears on each row, with the number of [still pending] for each customerid
    ???
    Thanks

    There are three issues here.
    1)  The condition in your subquery is incorrect.  It should be WHERE cu.customerid = o.customerid.
    2)  The results of your subquery are not in an aggregate or the GROUP BY clause.
    3)  You subquery is unnecessary.  Try the following.

    SELECT cu.customerid
    ,cu.company
    ,[still pending] = COUNT(OrderID)
    FROM customers cu
    LEFT JOIN orders o
    JOIN orderdetails ode on o.orderid = ode.orderid
    /*  process the LEFT JOIN after the orderdetails JOIN */
    on cu.customerid = o.customerid
        AND o.ordershipped = ''
    GROUP BY cu.customerid, cu.company

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply