Group By and Count

FAQ

Profile

Group By and Count

Database Help (Home) -> Programming

Next: Retrieving single text block from multiple record..

Author

Message

user102

External

Since: Oct 14, 2003
Posts: 25

(Msg. 1) Posted: Tue Jan 30, 2007 10:49 am Post subject: Group By and Count Archived from groups: microsoft>public>sqlserver>programming (more info?)

custid, date_logged_in 123, 1/1/2007 234, 1/1/2007 123, 1/1/2007 123, 1/1/2007 567, 1/1/2007 567, 1/2/2007 789, 1/2/2007 I only need to know that a custid logged in once per day. How would I get this count by time frame? E.g. the query for timeframe 2/2/2006 to 3/3/2008 would produce: date, logins 1/1/2007, 3 --although there are 5 entries for this day, there are only 3 distinct users that logged in 1/2/2007, 2 --2 distinct users Any thoughts or advice would be greatly appreciated Thank you. >> Stay informed about: Group By and Count

Anith Sen

External

Since: Feb 17, 2004
Posts: 310

(Msg. 2) Posted: Tue Jan 30, 2007 10:49 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You can use COUNT( DISTINCT custid ) with GROUP BY date_logged_in. Why do you have duplicate entries in the table? You might want to remove them and declare a key to avoid them. If multiple logins have to be tracked per day for a single customer, depending on your requirements, either you should either introduce a tallying attribute or use a more granular value for date_logged_in, say including the time portion. -- Anith >> Stay informed about: Group By and Count

John Doe

External

Since: Mar 31, 2007
Posts: 2

(Msg. 3) Posted: Tue Jan 30, 2007 10:49 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

msnews.microsoft.com wrote: > custid, date_logged_in > 123, 1/1/2007 > 234, 1/1/2007 > 123, 1/1/2007 > 123, 1/1/2007 > 567, 1/1/2007 > 567, 1/2/2007 > 789, 1/2/2007 > > ... > > date, logins > 1/1/2007, 3 --although there are 5 entries for this day, there are only > 3 distinct users that logged in > 1/2/2007, 2 --2 distinct users > SELECT custid , date_logged_in FROM <tablename> WITH (NOLOCK) GROUP BY custid , date_logged_in HAVING COUNT(1) > 0 >> Stay informed about: Group By and Count

Aaron Bertrand [SQL Serve

External

Since: Jan 10, 2008
Posts: 2166

(Msg. 4) Posted: Tue Jan 30, 2007 10:55 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

SELECT date_logged_in, COUNT(DISTINCT custid) FROM some_table_name GROUP BY date_logged_in; <msnews.microsoft.com> wrote in message > custid, date_logged_in > 123, 1/1/2007 > 234, 1/1/2007 > 123, 1/1/2007 > 123, 1/1/2007 > 567, 1/1/2007 > 567, 1/2/2007 > 789, 1/2/2007 > > I only need to know that a custid logged in once per day. How would I get > this count by time frame? E.g. the query for timeframe 2/2/2006 to > 3/3/2008 would produce: > > date, logins > 1/1/2007, 3 --although there are 5 entries for this day, there are only 3 > distinct users that logged in > 1/2/2007, 2 --2 distinct users > > Any thoughts or advice would be greatly appreciated > Thank you. > > >> Stay informed about: Group By and Count

user102

External

Since: Oct 14, 2003
Posts: 25

(Msg. 5) Posted: Tue Jan 30, 2007 11:06 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

thank you. what if I needed to sum the results to return the count of 5 in this case? "Aaron Bertrand [SQL Server MVP]" wrote in message > SELECT > date_logged_in, > COUNT(DISTINCT custid) > FROM > some_table_name > GROUP BY > date_logged_in; > > > > <msnews.microsoft.com> wrote in message > >> custid, date_logged_in >> 123, 1/1/2007 >> 234, 1/1/2007 >> 123, 1/1/2007 >> 123, 1/1/2007 >> 567, 1/1/2007 >> 567, 1/2/2007 >> 789, 1/2/2007 >> >> I only need to know that a custid logged in once per day. How would I >> get this count by time frame? E.g. the query for timeframe 2/2/2006 to >> 3/3/2008 would produce: >> >> date, logins >> 1/1/2007, 3 --although there are 5 entries for this day, there are only >> 3 distinct users that logged in >> 1/2/2007, 2 --2 distinct users >> >> Any thoughts or advice would be greatly appreciated >> Thank you. >> >> > > >> Stay informed about: Group By and Count

user102

External

Since: Oct 14, 2003
Posts: 25

(Msg. 6) Posted: Tue Jan 30, 2007 11:07 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thank you. There is a time value associated with the date logged in. I would like to restructure, however, at this point in time I just cant do it. "Anith Sen" wrote in message > You can use COUNT( DISTINCT custid ) with GROUP BY date_logged_in. > > Why do you have duplicate entries in the table? You might want to remove > them and declare a key to avoid them. If multiple logins have to be > tracked per day for a single customer, depending on your requirements, > either you should either introduce a tallying attribute or use a more > granular value for date_logged_in, say including the time portion. > > -- > Anith > >> Stay informed about: Group By and Count

Aaron Bertrand [SQL Serve

External

Since: Jan 10, 2008
Posts: 2166

(Msg. 7) Posted: Tue Jan 30, 2007 11:11 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

This query doesn't really make sense to me, but okay: SELECT SUM(c) FROM ( SELECT date_logged_in, c = COUNT(DISTINCT custid) FROM some_table_name GROUP BY date_logged_in ) x; <msnews.microsoft.com> wrote in message > thank you. what if I needed to sum the results to return the count of 5 > in this case? > > "Aaron Bertrand [SQL Server MVP]" wrote in > message >> SELECT >> date_logged_in, >> COUNT(DISTINCT custid) >> FROM >> some_table_name >> GROUP BY >> date_logged_in; >> >> >> >> <msnews.microsoft.com> wrote in message >> >>> custid, date_logged_in >>> 123, 1/1/2007 >>> 234, 1/1/2007 >>> 123, 1/1/2007 >>> 123, 1/1/2007 >>> 567, 1/1/2007 >>> 567, 1/2/2007 >>> 789, 1/2/2007 >>> >>> I only need to know that a custid logged in once per day. How would I >>> get this count by time frame? E.g. the query for timeframe 2/2/2006 to >>> 3/3/2008 would produce: >>> >>> date, logins >>> 1/1/2007, 3 --although there are 5 entries for this day, there are only >>> 3 distinct users that logged in >>> 1/2/2007, 2 --2 distinct users >>> >>> Any thoughts or advice would be greatly appreciated >>> Thank you. >>> >>> >> >> > >> Stay informed about: Group By and Count

user102

External

Since: Oct 14, 2003
Posts: 25

(Msg. 8) Posted: Tue Jan 30, 2007 11:14 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thank you. just to clarify what I need to do is get a count of all custids that logged in during a specific time frame. But if the cust logged in more than once per day then only count the login as 1. "Aaron Bertrand [SQL Server MVP]" wrote in message > This query doesn't really make sense to me, but okay: > > > SELECT SUM(c) FROM > ( > SELECT > date_logged_in, > c = COUNT(DISTINCT custid) > FROM > some_table_name > GROUP BY > date_logged_in > ) x; > > > > > <msnews.microsoft.com> wrote in message > >> thank you. what if I needed to sum the results to return the count of 5 >> in this case? >> >> "Aaron Bertrand [SQL Server MVP]" wrote in >> message >>> SELECT >>> date_logged_in, >>> COUNT(DISTINCT custid) >>> FROM >>> some_table_name >>> GROUP BY >>> date_logged_in; >>> >>> >>> >>> <msnews.microsoft.com> wrote in message >>> >>>> custid, date_logged_in >>>> 123, 1/1/2007 >>>> 234, 1/1/2007 >>>> 123, 1/1/2007 >>>> 123, 1/1/2007 >>>> 567, 1/1/2007 >>>> 567, 1/2/2007 >>>> 789, 1/2/2007 >>>> >>>> I only need to know that a custid logged in once per day. How would I >>>> get this count by time frame? E.g. the query for timeframe 2/2/2006 to >>>> 3/3/2008 would produce: >>>> >>>> date, logins >>>> 1/1/2007, 3 --although there are 5 entries for this day, there are >>>> only 3 distinct users that logged in >>>> 1/2/2007, 2 --2 distinct users >>>> >>>> Any thoughts or advice would be greatly appreciated >>>> Thank you. >>>> >>>> >>> >>> >> > > >> Stay informed about: Group By and Count

Aaron Bertrand [SQL Serve

External

Since: Jan 10, 2008
Posts: 2166

(Msg. 9) Posted: Tue Jan 30, 2007 11:16 am Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

> There is a time value associated with the date logged in. Then none of these queries are going to work, if you want the results by day. You would at least need to do: SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, date_logged_in)), COUNT(DISTINCT custid) FROM some_table_name GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, date_logged_in)); >> Stay informed about: Group By and Count

user102

External

Since: Oct 14, 2003
Posts: 25

(Msg. 10) Posted: Tue Jan 30, 2007 1:23 pm Post subject: Re: Group By and Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thanks for all of the help. Only one concern... what if I need to sum the results and return only one row? E.g. in this case the reult would be 5 "John Doe" wrote in message > msnews.microsoft.com wrote: > > custid, date_logged_in > > 123, 1/1/2007 > > 234, 1/1/2007 > > 123, 1/1/2007 > > 123, 1/1/2007 > > 567, 1/1/2007 > > 567, 1/2/2007 > > 789, 1/2/2007 > > > > ... > > > > date, logins > > 1/1/2007, 3 --although there are 5 entries for this day, there are only > > 3 distinct users that logged in > > 1/2/2007, 2 --2 distinct users > > > > SELECT > custid > , date_logged_in > FROM <tablename> WITH (NOLOCK) > GROUP BY > custid > , date_logged_in > HAVING COUNT(1) > 0 >> Stay informed about: Group By and Count

hadis ofatde

External

Since: Aug 18, 2010
Posts: 1

(Msg. 11) Posted: Wed Aug 18, 2010 3:27 am Post subject: use Sum on Count [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

if you want to use Sum on count can use below query (your query have a small mistake so attend this query; SELECT SUM(c) FROM ( SELECT date_logged_in, c = COUNT(DISTINCT custid) FROM some_table_name GROUP BY date_logged_in ) as m > On Tuesday, January 30, 2007 10:49 AM <msnews.microsoft.com> wrote: > custid, date_logged_in > 123, 1/1/2007 > 234, 1/1/2007 > 123, 1/1/2007 > 123, 1/1/2007 > 567, 1/1/2007 > 567, 1/2/2007 > 789, 1/2/2007 > > I only need to know that a custid logged in once per day. How would I get > this count by time frame? E.g. the query for timeframe 2/2/2006 to 3/3/2008 > would produce: > > date, logins > 1/1/2007, 3 --although there are 5 entries for this day, there are only 3 > distinct users that logged in > 1/2/2007, 2 --2 distinct users > > Any thoughts or advice would be greatly appreciated > Thank you. >> On Tuesday, January 30, 2007 10:55 AM Aaron Bertrand [SQL Server MVP] wrote: >> SELECT >> date_logged_in, >> COUNT(DISTINCT custid) >> FROM >> some_table_name >> GROUP BY >> date_logged_in; >>> On Tuesday, January 30, 2007 10:56 AM Anith Sen wrote: >>> You can use COUNT( DISTINCT custid ) with GROUP BY date_logged_in. >>> >>> Why do you have duplicate entries in the table? You might want to remove >>> them and declare a key to avoid them. If multiple logins have to be tracked >>> per day for a single customer, depending on your requirements, either you >>> should either introduce a tallying attribute or use a more granular value >>> for date_logged_in, say including the time portion. >>> >>> -- >>> Anith >>>> On Tuesday, January 30, 2007 11:06 AM <msnews.microsoft.com> wrote: >>>> thank you. what if I needed to sum the results to return the count of 5 in >>>> this case? >>>>> On Tuesday, January 30, 2007 11:07 AM <msnews.microsoft.com> wrote: >>>>> Thank you. There is a time value associated with the date logged in. I >>>>> would like to restructure, however, at this point in time I just cant do it. >>>>>> On Tuesday, January 30, 2007 11:11 AM Aaron Bertrand [SQL Server MVP] wrote: >>>>>> This query does not really make sense to me, but okay: >>>>>> >>>>>> >>>>>> SELECT SUM(c) FROM >>>>>> ( >>>>>> SELECT >>>>>> date_logged_in, >>>>>> c = COUNT(DISTINCT custid) >>>>>> FROM >>>>>> some_table_name >>>>>> GROUP BY >>>>>> date_logged_in >>>>>> ) x; >>>>>>> On Tuesday, January 30, 2007 11:14 AM <msnews.microsoft.com> wrote: >>>>>>> Thank you. just to clarify what I need to do is get a count of all custids >>>>>>> that logged in during a specific time frame. But if the cust logged in more >>>>>>> than once per day then only count the login as 1. >>>>>>> >>>>>>> >>>>>>> "Aaron Bertrand [SQL Server MVP]" wrote in message >>>>>>> >>>>>>>> On Tuesday, January 30, 2007 11:16 AM Aaron Bertrand [SQL Server MVP] wrote: >>>>>>>> Then none of these queries are going to work, if you want the results by >>>>>>>> day. You would at least need to do: >>>>>>>> >>>>>>>> SELECT >>>>>>>> DATEADD(DAY, 0, DATEDIFF(DAY, 0, date_logged_in)), >>>>>>>> COUNT(DISTINCT custid) >>>>>>>> FROM >>>>>>>> some_table_name >>>>>>>> GROUP BY >>>>>>>> DATEADD(DAY, 0, DATEDIFF(DAY, 0, date_logged_in)); >>>>>>>>> On Tuesday, January 30, 2007 11:32 AM John Doe wrote: >>>>>>>>> msnews.microsoft.com wrote: >>>>>>>>> >>>>>>>>> SELECT >>>>>>>>> custid >>>>>>>>> , date_logged_in >>>>>>>>> FROM <tablename> WITH (NOLOCK) >>>>>>>>> GROUP BY >>>>>>>>> custid >>>>>>>>> , date_logged_in >>>>>>>>> HAVING COUNT(1) > 0 >>>>>>>>>> On Tuesday, January 30, 2007 1:23 PM <msnews.microsoft.com> wrote: >>>>>>>>>> Thanks for all of the help. Only one concern... what if I need to sum the >>>>>>>>>> results and return only one row? E.g. in this case the reult would be 5 >>>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice >>>>>>>>>> Assemblies in Folder Debug Build Checker >>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/d7de5fe1-6155-4e81-96e1-98...d69d760 >> Stay informed about: Group By and Count

Display posts from previous:

Related Topics:

Group by count - Ive created a query with group by that utilizes COUNT however i don't want it to count 01/01/1900 eg 01/01/2006 02/01/2007 03/03/2006 01/01/1900 01/01/1900 count should be 3 is this possible using groupby ? cheers mark

select count of rows per group - I need to select the count of the first 3 rows per each group (xID) up to a count of 3 rows (top 3 rows - order by column x1) where the stat column is not null. if a group contains more than 3 rows where stat is not null - ignore the extra rows - onl...

select/isolate group from... where max(date in group) = so.. - In the following data I need to select (or isolate) the codes (code groups) which contain a max codeDate of '12/31/08'. Of the codes a, b, c, d -- only codes b and c contain a max codeDate of '12/31/08' in their respective groups. What would be the...

select rowcount per group before value in group = null - I need to select the top 3 rows per each group (xID -- order by xID). No problem here. But I need to select the count of rows in each group before a null value is encountered in the stat column -- ordering by xID CREATE TABLE #tmp1(rowID int..

Trying to Count - Here is the situation, I have multiple account numbers, and each account has multiple transactions. I first need to setup a count of transactions per account, which needs to be returned in my selection criteria.. then I need to setup criteria where I..

	Database Help (Home) -> Programming	All times are: Pacific Time (US & Canada)
Page 1 of 1

You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum