MySQL sum of two COUNT statements

Question

I know this has a stupid solution but, sorry, I'm little bit confused.

I have two SELECT COUNT statements. Example:

Statement 1

SELECT COUNT(softwareone) AS totalcount 
FROM my_table WHERE softwareone LIKE '%typeone%'

totalcount = 3 _

Statement 2

SELECT COUNT(softwaretwo) AS totalcount 
FROM my_table WHERE softwaretwo LIKE '%typeone%'

totalcout = 1

I want to sum both totals to get totalcount = 4. There is a way to do that?

Note: software type from columns "softwareone" and "softwaretwo" is of the same type (same value).

Thanks to all.

Is it on purpose that both selects filter on `%typeone%`? – Joachim Isaksson Mar 19 '12 at 18:37 — Joachim Isaksson, Mar 19 '12 at 18:37

score 4 · Accepted Answer · answered Mar 19 '12 at 18:36

One way is to write:

SELECT SUM(CASE WHEN softwareone LIKE '%typeone%'
                 AND softwaretwo LIKE '%typeone%'
                THEN 2
                ELSE 1
            END
          ) AS "totalcount"
  FROM my_table
 WHERE softwareone LIKE '%typeone%'
    OR softwaretwo LIKE '%typeone%'
;

The CASE ... END expression will evaluate to 2 when both conditions are met (so that if softwareone and softwaretwo are both LIKE '%typeone%', then the row counts twice), and 1 when only one of them is. So, the SUM(CASE ... END) gives the total number of rows where the one condition is met, plus the total number of rows where the other condition is met.

+1 The subquery solutions cause MySQL to scan through the records multiple times. This query only causes MySQL to scan through once. — Marcus Adams, Mar 19 '12 at 18:55

score 3 · Answer 2 · answered Mar 19 '12 at 18:42

You could use a

Select l1.totalcount + l2.totalcount FROM 
(SELECT COUNT(softwareone) AS totalcount 
FROM my_table WHERE softwareone LIKE '%typeone%') as l1,
(SELECT COUNT(softwaretwo) AS totalcount 
FROM my_table WHERE softwaretwo LIKE '%typeone%') as l2

MySQL sum of two COUNT statements

2 Answers2