KyloRen KyloRen - 4 months ago 11
SQL Question

SQL ,Count Data in column ,Undesired results?

I am counting a specific columns data occurance in SQL Server.

The criteria is to get a Staff members data via a unique Number assigned to that staff member. The count needs to be between two dates.

This is what I have thus far,
I am counting the value

Data_1
and how many times it occurs in the column name
Info_Data
,
BETWEEN '2016/6/15' AND '2016/7/16'
from the Column
Staff_No
who's number is
3201


SELECT S.Staff_No, S.Info_Data, C.cCount
FROM Staff_Manager.dbo.Staff_Time_TBL S
INNER JOIN (SELECT Info_Data, count('Data_1') as cCount
FROM Staff_Manager.dbo.Staff_Time_TBL
GROUP BY Info_Data) C ON S.Info_Data = C.Info_Data
WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/15' AND '2016/7/16'


The results are not what I was expecting and are as below.
Data_1
occurs 9 times between those two dates.

No | Staff_No | Info_Data | cCount
1 | 3201 | Data_1 | 3456
2 | 3201 | Data_1 | 3456
3 | 3201 | Data_1 | 3456
4 | 3201 | Data_1 | 3456
5 | 3201 | Data_1 | 3456
6 | 3201 | Data_1 | 3456
7 | 3201 | Data_1 | 3456
8 | 3201 | Data_1 | 3456
9 | 3201 | Data_1 | 3456


So it seems to be counting all the occurrences of
Data_1
in column
cCount
and repeating the rows
9
times. When it should look like this below.

No | Staff_No | Info_Data | cCount
1 | 3201 | Data_1 | 9


How to amend the SQL to get the desired results? I am guessing I have the
WHERE
in the wrong place, but I can't get anything else to work?

ZLK ZLK
Answer

Well, to get the desired results you just need to do a regular count with a group by statement.

SELECT Staff_No, Info_Data, COUNT(*) cCount
FROM Staff_Manager.dbo.Staff_Time_TBL
WHERE Staff_No = 3201 
AND Date_Data BETWEEN '2016/6/15' AND '2016/7/16'
GROUP BY Staff_No, Info_Data;

What your query was actually doing was getting each of the times it occurred as separate rows, and then showing a count of all the occurrences of Info_Data ('Data_1') in your Staff_Time_TBL (without caring about the date, because the date isn't part of the subquery in the join).