I'm working a DB design regarding how a user launched something. My idea was to have timestamp (DateTime) column, and a method column (varchar).
This 'method' (varchar) could be anything:
How can I COUNT the uses but group some values. In this case I want to have my result:
You need some way of defining which 'methods' fall into which 'method group'.
One way would be to have a lookup table:
tbl_methodgroup method_id Method Method_group 1 Button_OK Buttons 2 Button_X Buttons 3 App_Y App_Y 4 App_Z App_Z
then you could use:
select
b.method_group,
count(1)
from
tbl_methodgroup a
inner join tbl_method b on a.Method=b.Method
group by b.method_group
This method would have the advantage of being scalable as more methods get added. Rather than hand coding queries, which would need to be modified each time.
UNION a number of queries together to cope with the rules. It'd be a nightmare to maintain in the long term as the rules change or new methods are added to the list.If the name of the table is tblTest, then the query will look like following:
SELECT method, COUNT(*) FROM tblTEst Group BY method
Apologies if I missread question, last chance to make it right if you have consistency in the data and grouping scenarios you can do following:
SELECT LEFT(method,CHARINDEX('_',method)-1),
COUNT(*)
FROM tblTest
GROUP BY LEFT(method,CHARINDEX('_',method)-1)
Otherwise Stuart Moore's answer is correct one.
