CODEWITHSUNDEEP
sqlcount
AneeshaKK
AneeshaKK

Reputation: 105

Adding COUNT Statements in SQL

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.

Upvotes: 0

Views: 57

Answers (2)

David Julian
David Julian

Reputation: 1

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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.

Upvotes: 3

Related Questions