How to compute the quotient of total rows in one table and total rows in another table-CodePudding

I have two tables, stores and states, and I need (COUNT(*) of stores) / (COUNT(*) of states)

CREATE TABLE [dbo].stores
(
    [StoreID] [int] IDENTITY(1,1) NOT NULL,
    [Store] [nchar](200) NOT NULL,
    [StateID] [int] NOT NULL
)

CREATE TABLE [dbo].states 
(
    StateID [int] IDENTITY(1,1) NOT NULL,
    State [nchar](200) NOT NULL,
)

CodePudding user response：

You can solve this problem using simple query:

SELECT  
    -- get rows count from stores table
    (SELECT COUNT(*) FROM stores) / 
    -- divide by rows count from states table
    (SELECT COUNT(*) FROM states) AS Ratio;

online sql editor

CodePudding user response：

I don't have access to MSSQL currently, so the code below needs to be verified. Basically I'm calculating the counts within the with clause and then I;m using the results in the next query.

with stores as (
   select count(*) as stores_cnt from [dbo].stores
), states as (
   select count(*) as states_cnt from [dbo].states 
)
select stores.stores_cnt / states.states_cnt
from stores, states

Please note that in this version it will calculate the result for the whole table. If you would like to do it per any common column you would need to add a group by clause in the agg query and make a proper join in the end.