Click here to Skip to main content
Search within:



Next

Date Count in Sql basec on user id
Sign Up to vote bad
good
See more: SQL 
date            userid
2012-12-30          21
2012-12-30          21
2012-12-31          21
2012-12-30          22
I would like the result to look like
userid          totaldays
21                 2
22                 1

There are two same date because on same date they will have two entry ie AM and PM
 
I'm trying like this:
SELECT
CONVERT(date,TOF.compoffdate,101),
count(compoffdate) as TotalDays
FROM tblcompoff TOF
WHERE TOF.userid=21
group by tof.compoffdate
How to proceed?
Posted 4 Jan '13 - 1:47
tpkpradeep466
Edited 4 Jan '13 - 2:46
André Kraak50.9K
v3
Add a Solution


5 solutions

First you need to make sure you have a dataset on which you want to count the rows.
In your case you only want to count the unique days for an userid, so the first SQL query to achieve this would be 
SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
FROM tblcompoff
This query results in the following rows:
date            userid
2012-12-30          21
2012-12-31          21
2012-12-30          22
Now based on this data set we can count the number off days for each user id by using the following query: 
SELECT TOF.userid, COUNT( TOF.date ) AS totaldays
FROM (SELECT DISTINCT CONVERT( date, compoffdate, 101 ) AS date, userid
      FROM tblcompoff)
GROUP BY TOF.userid
The query performs the count over the first query we made to limit/specify our data.
The result is:
userid          totaldays
21                 2
22                 1
I hope this make the process off how to achieve your result clear.
  Permalink  
Posted 4 Jan '13 - 3:06
André Kraak50.9K
Edited 4 Jan '13 - 3:09
v3
select count(compoffdate),userid from tblcompoff  group by userid
 
Simple one i think its useful
  Permalink  
Posted 4 Jan '13 - 2:16
kankeyan1.2K
Comments
Arun1990 - 4 Jan '13 - 8:22
i thing you not see the result of your query, is, userid totaldays 21 3 22 1 so your solution is wrong
kankeyan - 4 Jan '13 - 8:31
I worked on this query it will work fine.
Use this
 
SELECT userid , COUNT(DISTINCT DATEADD(dd, 0, DATEDIFF(dd, 0, compoffdate))) AS totaldays FROM tblcompoff
GROUP BY userid
  Permalink  
Posted 4 Jan '13 - 2:53
Tharaka MTR5.9K
Edited 4 Jan '13 - 4:28
v2
Comments
Tharaka MTR - 4 Jan '13 - 10:27
Just check this and see. this works definitely
select cap.userID, Count(cap.Date) as date
from
(
    SELECT ROW_NUMBER() over (partition by convert(varchar, table.Date, 108), useriD Order by userID, convert(varchar, table.Date, 108)) as rownum, userID, convert(varchar, table.Date, 108) from table 
) as cap
where cap.RowNum=1
group by cap.userID
  Permalink  
Posted 4 Jan '13 - 3:06
digimanus22.7K
Edited 4 Jan '13 - 3:07
v2
Hi
 
You can use from bellow query(it's short way):
 
Select	UserID, Count(*)
From	(Select	UserID, Date
		From	[YourTableName]
		Group By	UserID, Date) As Temp
	Group by UserID
 
I hope it's helpful.
  Permalink  
Posted 4 Jan '13 - 4:12
Reza Alipour Fard918
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Mohammed Hameed 268
1 OriginalGriff 261
2 Sergey Alexandrovich Kryukov 178
3 Mayur_Panchal 153
4 Santhosh G_ 108
0 Sergey Alexandrovich Kryukov 8,171
1 OriginalGriff 6,246
2 CPallini 3,532
3 Rohan Leuva 2,703
4 Maciej Los 2,234


Advertise | Privacy | Mobile
Web02 | 2.6.130516.1 | Last Updated 4 Jan 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid