CODEWITHSUNDEEP
sqlgrouping
RvdK
RvdK

Reputation: 19790

SQL Group on a combination of values

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:

Upvotes: 0

Views: 74

Answers (2)

Farfarak
Farfarak

Reputation: 1527

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.

Upvotes: 1

Stuart Moore
Stuart Moore

Reputation: 191

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.

Upvotes: 2

Related Questions