January 5, 2006 at 4:20 pm
Hi,
I have a resultset that has two columns that are each derived from a correlated subquery, i need to add an additional column to show percentage but if i try to perform a maths function on the returned columns sql server says it doesn't know the column name.
Can anybody help ?
January 5, 2006 at 6:41 pm
You may be using an alias instead of its original attribute name.
January 6, 2006 at 3:54 am
I don't think it's as straight forward as that. Here is the query, I need to add another column showing the Renewal percentage of NumberRenewed against RenewalsDue.
SELECT res.AccountNumber, cust.AccountName, orders.WebID, COUNT(orders.ID) AS NumberOfSales, SUM(CAST(orders.TypeOfLicence AS int))
AS TotalLicences,
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59') AND (resords.Expiration >= '12/1/2005 00:00:00') AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0) AND (resords.WebID = orders.webid)) AS RenewalsDue,
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59') AND (resords.Expiration >= '12/1/2005 00:00:00') AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0) AND (resords.renewed = - 1) AND (resords.WebID = orders.webid)) AS NumberRenewed
FROM tblCustomers cust RIGHT OUTER JOIN
tblMEMResellers res ON cust.AccountNumber = res.AccountNumber RIGHT OUTER JOIN
tblMEMResellerOrders orders ON res.WebID = orders.WebID
WHERE (orders.ProcessDate >= '12/01/2005 00:00:00') AND (orders.ProcessDate <= '12/31/2005 23:59:59') AND (orders.PurchaseOption <> N'NFR')
GROUP BY orders.WebID, res.AccountNumber, cust.AccountName
ORDER BY res.AccountNumber
January 6, 2006 at 6:38 am
Impossible to test without table schemas and example data, but this should give you somewhere to start.
I moved the subqueries into the from clause so that the values returned can be referenced in the select without reruning the query.
SELECT res.AccountNumber, cust.AccountName, orders.WebID, COUNT(orders.ID) AS NumberOfSales,
SUM(CAST(orders.TypeOfLicence AS int)) AS TotalLicences,
DueOrders.NumberDue AS RenewalsDue,
RenewedOrders.NumberDue AS NumberRenewed,
RenewedOrders.NumberDue/DueOrders.NumberDue*100 AS RenewedPercentage
FROM tblMEMResellerOrders orders ON res.WebID = orders.WebID
LEFT OUTER JOIN tblMEMResellers res ON cust.AccountNumber = res.AccountNumber
LEFT OUTER JOIN tblCustomers cust
LEFT OUTER JOIN
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59')
AND (resords.Expiration >= '12/1/2005 00:00:00')
AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0)) DueOrders ON DueOrders.WebID = orders.webid
LEFT OUTER JOIN
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59')
AND (resords.Expiration >= '12/1/2005 00:00:00')
AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0)
AND (resords.renewed = - 1) ) RenewedOrders ON RenewedOrders.WebID = orders.webid
WHERE (orders.ProcessDate >= '12/01/2005 00:00:00')
AND (orders.ProcessDate <= '12/31/2005 23:59:59')
AND (orders.PurchaseOption <> N'NFR')
GROUP BY orders.WebID, res.AccountNumber, cust.AccountName
ORDER BY res.AccountNumber
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2006 at 10:18 am
hmm, looks great and i sort of get the idea but unfortuantely it doesn't compile in sql and returns an incorrect syntax near keyword ON. I think it has soimething to do with the naming of DueOrds (think it's missed from some field names) but not sure.
Really appreciate your reply
January 6, 2006 at 10:33 am
It doesn't compile because of this:
LEFT OUTER JOIN
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59')
AND (resords.Expiration >= '12/1/2005 00:00:00')
AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0)) DueOrders ON DueOrders.WebID = orders.webid
Moving it into the FROM creates a derived table, and the derived table must be standalone, not referencing columns in the other tables in the FROM.
You should re-write this to get the sub-selects out of the main select, because these are a performance nightmare waiting to happen. However, in the meantime, you can make your entire query 1 big derived table, and then calculate what you need:
SELECT dt.*, (NumberRenewed * 100.0) / RenewalsDue As PercentRenewed
FROM
( -- derived table starts here
SELECT res.AccountNumber, cust.AccountName, orders.WebID, COUNT(orders.ID) AS NumberOfSales, SUM(CAST(orders.TypeOfLicence AS int))
AS TotalLicences,
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59') AND (resords.Expiration >= '12/1/2005 00:00:00') AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0) AND (resords.WebID = orders.webid)) AS RenewalsDue,
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59') AND (resords.Expiration >= '12/1/2005 00:00:00') AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0) AND (resords.renewed = - 1) AND (resords.WebID = orders.webid)) AS NumberRenewed
FROM tblCustomers cust RIGHT OUTER JOIN
tblMEMResellers res ON cust.AccountNumber = res.AccountNumber RIGHT OUTER JOIN
tblMEMResellerOrders orders ON res.WebID = orders.WebID
WHERE (orders.ProcessDate >= '12/01/2005 00:00:00') AND (orders.ProcessDate <= '12/31/2005 23:59:59') AND (orders.PurchaseOption <> N'NFR')
GROUP BY orders.WebID, res.AccountNumber, cust.AccountName
) dt -- end of derived table
ORDER BY dt.AccountNumber
January 6, 2006 at 10:48 am
Wow, you can do that ? I never knew.
That really works 🙂 but I had to take out , (NumberRenewed * 100.0) / RenewalsDue As PercentRenewed, as i get a divide by zero, I bet there's a way to sort that, that i don't know aswell 🙁
I can see I'm going to have to do some studying, thanks very much for your help, greatly appreciated oh wise one . iF you do know how to cure the divide by zero ... 🙂
January 6, 2006 at 10:56 am
This should do it...
SELECT dt.*,
case when RenewalsDue = 0 then 0
else (NumberRenewed * 100.0) / RenewalsDue end As PercentRenewed
FROM
( -- derived table starts here
SELECT res.AccountNumber, cust.AccountName, orders.WebID, COUNT(orders.ID) AS NumberOfSales, SUM(CAST(orders.TypeOfLicence AS int))
AS TotalLicences,
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration = '12/1/2005 00:00:00') AND (resords.PurchaseOption 'NFR')
AND (resords.Cancelled = 0) AND (resords.WebID = orders.webid)) AS RenewalsDue,
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration = '12/1/2005 00:00:00') AND (resords.PurchaseOption 'NFR')
AND (resords.Cancelled = 0) AND (resords.renewed = - 1) AND (resords.WebID = orders.webid)) AS NumberRenewed
FROM tblCustomers cust RIGHT OUTER JOIN
tblMEMResellers res ON cust.AccountNumber = res.AccountNumber RIGHT OUTER JOIN
tblMEMResellerOrders orders ON res.WebID = orders.WebID
WHERE (orders.ProcessDate >= '12/01/2005 00:00:00') AND (orders.ProcessDate <= '12/31/2005 23:59:59') AND (orders.PurchaseOption N'NFR')
GROUP BY orders.WebID, res.AccountNumber, cust.AccountName
) dt -- end of derived table
ORDER BY dt.AccountNumber
January 6, 2006 at 11:03 am
I'm gobsmacked ! Never knew you could do that also 🙂 Ahh the joys of learning 🙂
Thank you very much once again, all works tickety boo 🙂
January 6, 2006 at 11:22 am
>>all works tickety boo
Heh, until the tables get large enough and those cursor-like sub selects start running like molasses 🙂
January 9, 2006 at 12:14 am
It doesn't compile because of this:
LEFT OUTER JOIN
(SELECT COUNT(ID) AS NumberDue
FROM tblMEMResellerOrders ResOrds
WHERE (resords.Expiration <= '12/30/2005 23:59:59')
AND (resords.Expiration >= '12/1/2005 00:00:00')
AND (resords.PurchaseOption <> 'NFR')
AND (resords.Cancelled = 0)) DueOrders ON DueOrders.WebID = orders.webid
Actually, it wouldn't have compiled because I left a stray on clause when I rearranged the from clause
FROM tblMEMResellerOrders orders ON res.WebID = orders.WebID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply