Counting strings in SQL Server by substrings
I am using SQL Server 2008 R2. I currently have a table that looks like this:
Text DateLogged SomeID SomeOtherID
-----------------------------------------------
1234: Data 04/04/2014 1 190
3212: Text 04/04/2014 1 190
4332: Data 04/04/2014 1 190
1256: Data 04/04/2014 1 190
and also the following SQL query:
SELECT RIGHT(Text, LEN(Text) - CHARINDEX(':', Text) - 1) AS Sub, DateLogged, SomeID, SomeOtherID
FROM Example
WHERE SomeOtherID = 190
I would like to get the row counts with the same substring (Sub) for a report I am creating with SSRS. However, I cannot find a way to group the query results by substring (FYI, DateLogged and SomeID fields are used for grouping in the report). The report will look like this:
Month SomeID Sub Count
---------------------------------
April
1
Data 3
Text 1
Any solution is welcome, be it at the query level or the report level!
+3
source to share
1 answer
This is counting substrings:
SELECT RIGHT(Text, LEN(Text) - CHARINDEX(':', Text) - 1) AS Sub, count(*)
FROM Example
WHERE SomeOtherID = 190
GROUP BY RIGHT(Text, LEN(Text) - CHARINDEX(':', Text) - 1);
If you want a date and a different id:
SELECT datepart(month, datelogged) as mon,
RIGHT(Text, LEN(Text) - CHARINDEX(':', Text) - 1) AS Sub,
someotherid, count(*)
FROM Example
WHERE SomeOtherID = 190
GROUP BY datepart(month, datelogged) as mon,
RIGHT(Text, LEN(Text) - CHARINDEX(':', Text) - 1),
SomeOtherId;
+2
source to share