Marv Marv - 7 months ago 12
SQL Question

Count and name content from a SQL Server table

I have a table which is structured like this:

+-----+-------------+-------------------------+
| id | name | timestamp |
+-----+-------------+-------------------------+
| 1 | someName | 2016-04-20 09:41:41.213 |
| 2 | someName | 2016-04-20 09:42:41.213 |
| 3 | anotherName | 2016-04-20 09:43:41.213 |
| ... | ... | ... |
+-----+-------------+-------------------------+


Now, I am trying to create a query, which selects all timestamps since time x and count the amount of times the same name occurs in the result.

As an example, if we would apply this query to the table above, with
2016-04-20 09:40:41.213
as the date from which on it should be counted, the result should look like this:

+-------------+-------+
| name | count |
+-------------+-------+
| someName | 2 |
| anotherName | 1 |
+-------------+-------+


What I have accomplished so far is the following query, which gives me the the names, but not their count:

WITH screenshots AS
(
SELECT * FROM SavedScreenshotsLog
WHERE timestamp > '2016-04-20 09:40:241.213'
)
SELECT s.name
FROM SavedScreenshotsLog s
INNER JOIN screenshots sc ON sc.name = s.name AND sc.timestamp = s.timestamp
ORDER BY s.name


I have browsed through stackoverflow but was not able to find a solution which fits my needs and as I am not very experienced with SQL, I am out of ideas.

Answer

You mention one table in your question, and then show a query with two tables. That makes it hard to follow the question.

What you are asking for is a simple aggregation:

SELECT name, COUNT(*)
FROM SavedScreenshotsLog
WHERE timestamp > '2016-04-20 09:40:241.213'
GROUP BY name
ORDER BY COUNT(*) DESC;

EDIT:

If you want "0" values, you can use conditional aggregation:

SELECT name,
       SUM(CASE WHEN timestamp > '2016-04-20 09:40:241.213' THEN 1 ELSE 0 END) as cnt
FROM SavedScreenshotsLog
GROUP BY name
ORDER BY cnt DESC;

Note that this will run slower because there is no filter on the dates prior to aggregation.

Comments