mysql select count for multiple conditions

Question

I have a table that contains elements.

Each element has its parent instance_id, a number_of_lines of code, and a deletion_date when removed.

I built a query that counts the elements grouped by instance with totals for elements that have code ( number_of_lines not zero) and elements that have been removed (deletion_date not null)

SELECT instance_id, 
    COUNT(id) AS elementsfound,
    COUNT(NULLIF(number_of_lines,0)) AS havecode,
    (COUNT(id)-COUNT(NULLIF(number_of_lines,0))) AS havenocode,      
    COUNT(deletion_date) AS beenremoved,          
    (COUNT(id)-COUNT(deletion_date)) AS stillexistent        
  FROM elements        
  GROUP BY instance_id

and I obtain a correct result of (abbreviated)

+-------------+---------------+----------+------------+-------------+---------------+
| instance_id | elementsfound | havecode | havenocode | beenremoved | stillexistent |
+-------------+---------------+----------+------------+-------------+---------------+
|          59 |         93123 |    24109 |      69014 |       45397 |         47726 |
|          69 |         46399 |     7837 |      38562 |       23929 |         22470 |
|          71 |         41752 |     8746 |      33006 |        6960 |         34792 |
|          75 |         29097 |     4303 |      24794 |       13670 |         15427 |
|          77 |         41681 |     9858 |      31823 |       10540 |         31141 |
|          79 |         17800 |      695 |      17105 |       13391 |          4409 |
|          82 |         25323 |     2481 |      22842 |       20914 |          4409 |
|          83 |         12831 |     2544 |      10287 |        2988 |          9843 |

...

Now, I want to add a column with the number of elements per instance that both are still existent (deletion_date is null) and have code (number_of_lines are different than zero)

I have found no way to either add a NULLIF nor a CASE WHEN with multiple columns.

How can I include a multiple-column-conditional counter?

slaakso · Answer 1 · 2020-01-15T17:55:41.757

1

Are you trying to do?

sum( if(deletion_date is null and number_of_lines!=0, 1, 0) )

edited Jan 15 '20 at 17:55

answered Jan 15 '20 at 17:06

slaakso

8,331
2
16
27

yes, +1 that approaches it too, eventhoug its the reverse of what I was wanting to do (@Pan has also fixed my intention as well) – PA. Jan 15 '20 at 17:53

score 1 · Accepted Answer · answered Jan 15 '20 at 17:13

I assume you mean that still existent records should have deletion_date NULL.

Try this:

SELECT instance_id, 
    COUNT(id) AS elementsfound,
    COUNT(NULLIF(number_of_lines,0)) AS havecode,
    (COUNT(id)-COUNT(NULLIF(number_of_lines,0))) AS havenocode,      
    COUNT(deletion_date) AS beenremoved,          
    (COUNT(id)-COUNT(deletion_date)) AS stillexistent,
    COUNT(IF(deletion_date IS NULL AND number_of_lines > 0, 1, NULL)) existentwithcode
  FROM elements        
  GROUP BY instance_id

mysql select count for multiple conditions

2 Answers2