• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

VB.net SQL Count number of x and -

Hi

I am using the following SQL query to get the result below

Select [Date],[Time],[UserID],[Name],[Gratitude], [Reframing], [Empathy], [Adaptable], [Teamwork] From Diary2 Where CompanyID = '" & Me.Label_Company.Text & "'"

How do I modify the query to get a count of the number of "x" entries for each email address
in the column UserID


1
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
rshqCommented:
Hi

please test this query

select UserId,sum(Countx) as Totalx
from (select  UserId,
            (case when [Gratitude]='x' 1 else 0)+
            (case when [Reframing]='x' 1 else 0)+
            (case when [Empathy]='x' 1 else 0)+
            (case when [Adaptable]='x' 1 else 0)+
            (case when [Teamwork]='x' 1 else 0) as Countx
 From Diary2 Where CompanyID = '" & Me.Label_Company.Text & "'")
group by UserId

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Another way is to add a subquery in your T-SQL that counts the email addresses, then JOIN to your Dairy2 table, then add the count to your SELECT clause
Select [Date],[Time],Dairy2.[UserID], uic.UserID_Count, [Name],[Gratitude], [Reframing], [Empathy], [Adaptable], [Teamwork] 
From Diary2 
  JOIN (SELECT UserID, COUNT([UserID]) as UserID_Count FROM Diary2 Where CompanyID = '" & Me.Label_Company.Text & "' GROUP BY UserID) uic ON Dairy2.UserID = uic.UserID
Where CompanyID = '" & Me.Label_Company.Text & "'

Open in new window


<edit>
Disregard, didn't read the question correctly.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now