February 8, 2017 at 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
February 10, 2017 at 6:47 am
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 🙂
February 10, 2017 at 9:39 am
Zososql - Wednesday, February 8, 2017 5:32 PMI 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) fromorders 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.orderidGROUP 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