Adding COUNT Statements in SQL

Question

I'm trying to add count statements in SQL that have conditions, and I keep getting an error. Below is my code:

  SELECT 
  (100.0*(COUNT(CASE [TYPE] WHEN 500 THEN 1 ELSE NULL END)) / (100*
  (COUNT(CASE [TYPE] WHEN 400 THEN 1 ELSE NULL END + COUNT(CASE [TYPE] WHEN 
  300 THEN 1 ELSE NULL END))) AS Ratio
  FROM historytable

Basically I'm trying to get the percentage of count of activity type 500 divided by count of activity type 400 and 300, and I keep getting an error.

What's the error? What RDBMS are you using?

SS_DBA
– SS_DBA

2017-06-21 18:08:02 +00:00
Commented Jun 21, 2017 at 18:08 — SS_DBA
– SS_DBA, Commented Jun 21, 2017 at 18:08
Change the NULL to 0. That's probably your issue.

SS_DBA
– SS_DBA

2017-06-21 18:08:51 +00:00
Commented Jun 21, 2017 at 18:08 — SS_DBA
– SS_DBA, Commented Jun 21, 2017 at 18:08

Gordon Linoff · Accepted Answer · 2017-06-21 18:09:47Z

3

I would write this as:

 SELECT (sum(case when [type] = 500 then 100.0 else 0 end) /
         sum(case when [type] in (300, 400) then 1 end)
        ) as ratio
 FROM historytable;

I prefer sum() to count() for this type of calculation (personal preference, count() is fine). Your problem is a missing paren in the denominator.

answered Jun 21, 2017 at 18:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Julian · Accepted Answer · 2017-06-21 21:58:53Z

Try something like this.

SELECT (
  100.0 *(
      SELECT count( * )
      FROM historytable
      WHERE [type]=500
  ) / ( 
    100 * (
      (
        SELECT count( * )
        FROM historytable
        WHERE [type]=400
      ) + (
        SELECT count( * )
        FROM historytable
        WHERE [type]=300
      )
    )
  )
) as ratio

Using a mysql database I was able to test this on a data table with [type] and values specific to that table.

SELECT (
  100.0 *(
      SELECT count(*) FROM my_table WHERE record_type='value_1'
  ) / ( 
      100 *(
          (
              SELECT count(*) FROM my_table WHERE record_type='value_2'
          ) + (
              SELECT count( * ) FROM my_table WHERE record_type='value_3'
          )
      )
  )
) as ratio;
+---------+
| ratio   |
+---------+
| 1.61804 |
+---------+
1 row in

 set (0.00 sec)

Collectives™ on Stack Overflow

Adding COUNT Statements in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related