How can I count duplicate record values and tally? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can I count duplicate record values and tally?

Author

Topic

maris60
Starting Member

2 Posts

Posted - 2002-04-10 : 18:26:46

I have tried using SQL to do this and count the values, however, I am unable to get it done. I have also tried with ASP with no success. I am trying to count the number of records for a particular given day and write it to the page in the following format. I would prefer to do it with ASP. Anyone have any ideas? I have been playing with this code for 4 days now. Please help! Thanks.

Date......................Total Records For Date

4/19/2002..............3

4/22/2002..............8

4/23/2002..............2

5/2/2002................1

5/20/2002..............1

Total Records.........15

My current SQL statement pulls the following information.

4/19/2002
4/19/2002
4/19/2002
4/22/2002
4/22/2002
4/22/2002
4/22/2002
4/22/2002
4/22/2002
4/22/2002
4/22/2002
4/23/2002
4/23/2002
5/2/2002
5/20/2002

My current SQL statement that pulls the above information.

SQLQuery = "SELECT Max(tbl_Table.myDate) AS FollowupDate, " _
& "my_Input.myStatus, my_Input.myNo " _
& "FROM my_Input INNER JOIN tbl_Table ON my_Input.myNo = tbl_Table.myHistoryid " _
& "GROUP BY my_Input.myStatus, my_Input.myNo " _
& "HAVING (((my_Input.myStatus)='OPEN')) OR (((my_Input.myStatus)='PENDING')) " _
& "ORDER BY Max(tbl_Table.myDate);"

marileng
Starting Member

28 Posts

Posted - 2002-04-10 : 20:31:51

try this

SQLQuery = " select dt.followupdate,count(dt.followupdate) from (SELECT Max(tbl_Table.myDate) AS FollowupDate, " _
& "my_Input.myStatus, my_Input.myNo " _
& "FROM my_Input INNER JOIN tbl_Table ON my_Input.myNo = tbl_Table.myHistoryid " _
& "GROUP BY my_Input.myStatus, my_Input.myNo " _
& "HAVING (((my_Input.myStatus)='OPEN')) OR (((my_Input.myStatus)='PENDING'))) dt " _
& "ORDER BY dt.followupdate);"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-10 : 20:32:37

Ummm... THAT select statement (in original post) pulls more than just the data you showed it does. You're only showing dates.

Regardless, what you need is GROUP BY. In general it would look like this:

SELECT DateField, Count(*)
FROM MyTable
GROUP BY DateField
ORDER BY DateField

You can look up the GROUP BY syntax in BOL and see the examples they have using WITH ROLLUP to get the subtotal at the end. Now, given the initial SELECT statement you said you are using, you might be able to use your statement as a derived table in the above FROM clause, or you might want to insert the results of your SELECT into a temp table and then run the above statement on that temp table.

WARNING, if you are trying to include all of the other fields in your output, we need to know that because it will really mess up the GROUP BY statement.

blasted snipers!...
------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 04/10/2002 20:34:47

marileng
Starting Member

28 Posts

Posted - 2002-04-10 : 20:46:47

sorry AjarnMark is right I did not noticed your query I thought it was only resulting the date.

You should have a group by clause with the date field.

maris60
Starting Member

2 Posts

Posted - 2002-04-11 : 07:23:53

I do need those other fields as far as meeting the criteria (i.e. OPEN, Pending Status), however, I don't need to display this information on the page. I appreciate all the help. I will try it out tonight and let you know how it works. Thank you.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-04-14 : 23:32:44

Just as a sanity check, you don't have to have a field in the select list to check anything on it or to include it in the group by. (If it's in the having list, I believe ANSI SQL requires it, but I don't think T-SQL does.)

This will probably become more important to you when you start using subqueries.

As another note, this particular HAVING query would usually be better off being a WHERE clause. This would help keep the server from grouping and counting all of the types you don't care about.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources