Alex Rivera | Logout

Beginner SQL question: arithmetic with multiple COUNT(*) results

Asked 2010-05-29T07:24:50.577
9

Continuing with the spirit of using the Stack Exchange Data Explorer to learn SQL, (see: Can we become our own “Northwind” for teaching SQL / databases?), I've decided to try to write a query to answer a simple question (on meta): What % of stackoverflow users have over 10,000 rep?.

Here's what I've done:

Query#1

SELECT COUNT(*)
FROM Users
WHERE
  Users.Reputation >= 10000

Result:

556

Query#2

SELECT COUNT(*)
FROM
  USERS

Result:

227691

Now, how do I put them together into one query? What is this query idiom called? What do I need to write so I can get, say, a one-row three-column result like this:

556     227691      0,00244190592
sql statistics count
Edit
Report

3 Answers

3

Apart from using CTEs, in this case you could also have done:

SELECT CAST((SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000) AS float)  /
       (SELECT COUNT(*) FROM USERS) * 100  AS Percentage​

The cast as float was to force a floating-point division, because with integer division 556 / 227691 would give 0.

answered 2010-05-29T07:37:43.080
3

Thanks to the other answers here, I've written the following queries, all of which work on SEDE:

"Inline view"

SELECT *, CAST([10K] AS FLOAT)/[All] AS [Ratio]
FROM (
   SELECT
    (SELECT COUNT(*) FROM Users) AS [All],
    (SELECT COUNT(*) FROM Users Where Reputation >= 10000) AS [10K]
) AS UsersCount

(See query result)

Variables

DECLARE @numAll FLOAT
DECLARE @num10kers FLOAT

SET @numAll = (SELECT COUNT(*) FROM Users)
SET @num10kers = (SELECT COUNT(*) FROM Users WHERE Users.Reputation >= 10000);

SELECT  @num10kers AS [10K], @numAll AS [All], @num10Kers/@numAll AS [Ratio]

(See query result)

References

Common Table Expression

WITH Users10K AS ( 
    SELECT COUNT(*) AS Count
    FROM Users
    WHERE Users.Reputation >= 10000
), UsersAll AS (
    SELECT COUNT(*) As Count
    FROM Users
)
SELECT
    Users10K.Count AS [10K],
    UsersAll.Count AS [All],
    CAST(Users10K.Count AS FLOAT) / UsersAll.Count AS [Ratio]
FROM Users10K, UsersAll

(See query result)

References

answered 2010-05-29T10:04:23.393
3

For queries like this, where I'm doing multiple counts on a single table based on different criteria, I like to use SUM and CASE:

SELECT
    UsersCount.[10K],
    UsersCount.[All],
    (CAST(UsersCount.[10K] AS FLOAT) / UsersCount.[All]) AS [Ratio]
FROM
    (SELECT
         SUM(CASE
               WHEN Users.Reputation >= 10000 THEN 1
               ELSE 0
             END) AS [10K],
         COUNT(*) AS [All]
     FROM Users) AS UsersCount

(query results)

The advantage is that you're only scanning the Users table once, which may be significantly faster.

answered 2010-05-30T21:34:00.647

Your Answer