Menu
Does anyone know how can I do a count in SQL Server based on condition.
Example:
How can I do a column count for records with name 'system', and total CaseID records in the table?
Customer table
UserID CaseID Name 1 100 alan 1 101 alan 1 102 amy 1 103 system 1 104 ken 1 105 ken 1 106 system The result will display like below:
UserID TotalCaseID TotalRecordsWithSystem 1 7 2 
473
COUNT() with HAVINGThe HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. The HAVING clause is used instead of WHERE clause with SQL COUNT() function.
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
Use SUM/CASE...
SELECT COUNT(*), --total SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) --conditional FROM myTable If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
