Using Count is a Proc
-
Hi All,
I need to be able to use count() in a stored proc and I'm not sure how I can do it. The following proc is what I need to do, but this throws up an error :
--********************************
SELECT
dbo.Orders.OrderNumber,
dbo.Orders.OrderDate,
dbo.Orders.OrderValue
FROM
dbo.Advantage
INNER JOIN
dbo.Customer ON dbo.Advantage.CustomerRef = dbo.Customer.CustomerID
INNER JOIN
dbo.Orders ON dbo.Customer.CustomerID = dbo.Orders.CustomerRef
having(count(dbo.Orders.OrderNumber) > 1)
where dbo.Orders.OrderNumber like 'CTW%' and dbo.Orders.StatusID = 50
order by OrderNumber
--*********************************
I realize the statement 'having(count(dbo.Orders.OrderNumber) > 1)' is what throws the error, but I am not sure how to get what I want from the query.
Basically I need to be able to get all order information where the count of the same order number is > 1.
Can anybody help me out?
-
You will need to have the structure of your query go:
FROM then WHERE then GROUP BY then HAVING and finally ORDER BY
The GROUP BY is required for the COUNT() to work.
Try rearranging the query and add the GROUP BY and you should be ok.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Two possible methods are:
SELECT dbo.Orders.OrderNumber,
dbo.Orders.OrderDate,
dbo.Orders.OrderValue
FROM dbo.Advantage
INNER JOIN dbo.Customer ON dbo.Advantage.CustomerRef = dbo.Customer.CustomerID
INNER JOIN dbo.Orders ON dbo.Customer.CustomerID = dbo.Orders.CustomerRef
WHERE dbo.Orders.OrderNumber like 'CTW%' and dbo.Orders.StatusID = 50
AND (SELECT Count(dbo.Orders.OrderNumber)
FROM dbo.Orders ord2
WHERE ord2.OrderNumber = dbo.Orders.OrderNumber) > 1
ORDER BY OrderNumber
-- or --
SELECT dbo.Orders.OrderNumber,
dbo.Orders.OrderDate,
dbo.Orders.OrderValue
-- , dOrders.d_Count
FROM dbo.Advantage
INNER JOIN dbo.Customer ON dbo.Advantage.CustomerRef = dbo.Customer.CustomerID
INNER JOIN dbo.Orders ON dbo.Customer.CustomerID = dbo.Orders.CustomerRef
INNER JOIN (SELECT dOrd.OrderNumber AS d_OrdersNumber,
Count(dOrd.OrderNumber) AS d_Count
FROM dbo.Orders dOrd
WHERE dOrd.OrderNumber like 'CTW%'
AND dOrd.StatusID = 50
GROUP BY dOrd.OrderNumber
) AS dOrders
ON dbo.Orders.OrderNumber = dOrders.OrderNumber
WHERE dbo.Orders.OrderNumber LIKE 'CTW%'
AND dbo.Orders.StatusID = 50
AND dOrders.d_Count > 1
ORDER BY OrderNumber
-
Just to supply some variation
If the only criteria is 'rows from order where the same ordernumber is in more than one row', then this is the basic query giving you that:
SELECT OrderNumber
FROM dbo.Orders
WHERE OrderNumber like 'CTW%'
AND StatusID = 50
GROUP BY OrderNumber
HAVING COUNT(*) > 1
Then you can take this list as a virtual table and join against orders again to retrieve the rest of the order details;
SELECT o.orderNumber,
o.OrderDate,
o.OrderValue
FROM ( SELECT OrderNumber
FROM dbo.Orders
WHERE OrderNumber like 'CTW%'
AND StatusID = 50
GROUP BY OrderNumber
HAVING COUNT(*) > 1
) x
JOIN dbo.Orders o
ON o.OrderNumber = x.OrderNumber
If you need further details, you can just add joins on customer and Advantage at the bottom, depending on in which tables columns live that you want to display (or possibly filter by joining)
/Kenneth
-
Wow, Thanks so much Guys, I eagerly waited for the answer the other night, and when AJ Ahrens answered I used that reply. This did have the limitation that it only showed up one record for each instance of order number (that had 2 or more of the same), but I was happy with that because it did what I wanted - returned me only rows where there were 2 or more of the same. I think this forum is pretty cool. I have even found answers to question I never even knew existed. As you can probably tell, I'm a newbie.
Thanks again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply