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.
| Author |
Topic |
|
dachish
Starting Member
12 Posts |
 Posted - 2008-12-08 : 16:40:52
|
| Good day all,I'm quite new at all of this and I'm thinking this problem might be a very simple thing. I hope! =)I have a database that is the db for our ticketing system for the helpdesk. I want a query that basically says "look at all tickets with the category of "password reset" older than a given date range and then filter by username so that if a given username appears two or more times, show the results"So really all I want is, let's say, there have been 3 password resets (if only!) in the last two weeks. One was from Bob Smith. Two others were from Frank Jones. I only want to see that Frank Jones called twice, and I want to display the fact that Frank called in twice.The final goal is to create a report to show users needing their password reset more than two times in two weeks.The hurdle I have run into is I know how to get MOST of those results to show, but I don't know how to count the number of results and then show them if a username shows up more than two times. Basically I don't know how to say "if the username shows up >=1, show me the info".What I have now is:SELECT mantis_bug_table.id AS Ticket, mantis_bug_table.category AS Category, mantis_bug_table.date_submitted AS `Date Submitted`, mantis_contact_table.unameFROM mantis_bug_table, mantis_contact_tableWHERE mantis_bug_table.id = mantis_contact_table.bug_id AND (mantis_bug_table.date_submitted >= '2008-11-20') AND (mantis_bug_table.category = 'security - password reset')So all that will show me: the ticket numbers of any user who called in with that category, the date it was submitted, and their username. I'm at a loss on how to then count the results of any usernames that appear more than once and display them.I tried afew things with COUNT but so far no luck. Thanks so much! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-08 : 16:42:41
|
COUNT(*) OVER (PARTITION BY username)
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
dachish
Starting Member
12 Posts |
Posted - 2008-12-08 : 16:51:25
|
| Oops I should also mention this db is in MySQL. Hopefully that doesn't disqualify me from posting =)Thanks Peso, I've never used that function before. I will go give it a try! |
|
|
|
dachish
Starting Member
12 Posts |
Posted - 2008-12-08 : 17:56:35
|
Peso - I tried that and it doesn't like it. I can of course do the COUNT(*) just fine but it doesn't like the partition bit. Looks like maybe MySQL syntax is slightly different. I'm looking up what the difference in syntax might be. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Counting Number of Results?