Muzafar Khan Muzafar Khan - 5 months ago 8
SQL Question

Get Counts according to Last Date in SQL Server

I have this data in a table:

+-------------+-------+----------------+
| id | Added | Removed | Date |
+-------------+-------+----------------+
| 1 | yes | yes | 06-19-2016 |
| 2 | yes | yes | 06-19-2016 |
| 3 | yes | false | 06-19-2016 |
| 4 | false| yes | 09-19-2016 |
| 5 | yes | yes | 09-19-2016 |
| 6 | false| false | 10-19-2016 |
| 7 | false| yes | 10-19-2016 |
| 8 | yes | false | 10-19-2016 |
| 9 | false| yes | 11-19-2016 |
+-------------+-------+-----------------+


I know total added and removed record for date wise is like

Date TotalRecordAdded TotalRecordRemoved
06-19-2016 3 2
09-19-2016 1 2
10-19-2016 1 1
11-19-2016 0 1


What is the SQL Server query for getting this result?

Answer

You can do it with conditional aggregation using CASE EXPRESSION :

SELECT t.date,
       COUNT(CASE WHEN t.added = 'yes' THEN 1 END) as TotalRecordAdded,
       COUNT(CASE WHEN t.removed = 'yes' THEN 1 END) as TotalRecordRemoved
FROM YourTable t
GROUP BY t.date
Comments