Divide counts within table

Last Post 29 Oct 2009 12:15 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
klanners
New Member
New Member

--
14 Sep 2009 12:47 PM
This doesn't seem like it should be this hard... maybe it's just a Monday thing.

I've got some data in a table and I want to divide a subset of it by another. Specifically when I'm looking at a specific status and then the number of records with that status over 90 days old. I've written the query below but I get an incorrect syntax error at the , between the two selects. Now I'm stumped.

select x / y
from
(select count(*) x from My_Table where Status = 'O' and Days_Old < '90'),
(select count(*) y from My_Table where Status = 'O')
SQLUSA
New Member
New Member

--
29 Oct 2009 12:15 PM
You don't even need the FROM clause since the SELECTs return scalar values:

SELECT 1.0 * (SELECT COUNT(*) FROM Sales.SalesOrderHeader) /
(SELECT COUNT(*) FROM Sales.SalesOrderDetail)
-- 0.259361837170

Kalman Toth, SQL Server 2005 Training
http://www.sqlusa.com


Acceptable Use Policy
---