SQL Count How To? 1

[Richo1980]

Hi,

I am trying to extract information from a database using an SQL statement.

In this database I have 2 columns HPDName and HPDAssigned. Within the HPDAssigned Column I have one of 2 things, Admin or Support. In the HPDName Column I have the name of the Technician. What I want to show on my page is the name of the technician and how many calls they closed in each of the groups (Admin and Support)

I know how to do a COUNT to get the total number of records in the HPDAssigned column but I don't know how to show the Name of the Technician and how many calls they closed individually

Here's what I'm using for the total count for both Admin and Support from HPDAssigned

rs.Open "Select COUNT([HPDAssigned]) FROM HD WHERE [HPD Assigned] = 'Admin'", conn

rs.Open "Select COUNT([HPDAssigned]) FROM HD WHERE [HPD Assigned] = 'Support'", conn

Can anyone advise?

 

[AlexCuse]

something like this?

Notice the use of CASE and SUM, to provide the COUNT you are looking for.

[COLOR=blue]select[/color] TechName,
sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] [HPD Assigned] = [COLOR=red]'Admin'[/color] [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] [COLOR=blue]Admin[/color],
sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] [HPD Assigned] =[COLOR=red]'Support'[/color] [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] Support
[COLOR=blue]from[/color] HD
[COLOR=blue]group[/color] [COLOR=blue]by[/color] TechName

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Oh wait, aren't you using Access? If so,

select TechName,
sum(iif([HPD Assigned] = 'Admin', 1, 0)) as Admin,
sum(iif(HPD Assigned] ='Support', 1, 0)) as Support
from HD
group by TechName

Hope this helps and I repeat there is an Access forum here forum701

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Richo1980]

Thanks so much Alex...Worked perfectly

I apologise for posting in the wrong area will make sure I post in Forum701 next time

Cheers

 

[AlexCuse]

No problem, its' a big place, easy to get lost in

Thx for the purple thingy

Ignorance of certain subjects is a great part of wisdom

 

[Richo1980]

Sorry Alex, just one more question on this (please let me know if I should ask this in the other forum)

But how do I order the results by total (i.e best to worst)

I tried

ORDER BY 'Support' DESC

But this does not seem to work?

 

[micang]

Hi Richo1980,

Remove the ' ' around 'Support'

ORDER BY Support

I think that's the problem

Michael

 

[AlexCuse]

Sure thing, you need to order by

sum(iif(HPD Assigned] ='Support', 1, 0)) desc

or remove the quotes (I am not sure access will let you order by calculated column name, but it might)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Richo1980]

That gives me an error

No value given for one or more required parameters.

 

[AlexCuse]

I just tested, and removing the quotes won't help. You need to place the entire calculation in your order by.

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[micang]

Alex is correct, the below should do the trick.

SELECT TechName, 
Sum(IIf([HPD Assigned]='Admin',1,0)) AS Admin,
Sum(IIf([HPD Assigned]='Support',1,0)) AS Support
FROM HD
GROUP BY TechName
ORDER BY Sum(IIf([HPD Assigned]='Support',1,0)) DESC;

Michael

 

[Richo1980]

Your suggestion allows me to bring up the query in my webpage Michael but the order is still out..ie the Total's don't flow down the page in order..

 

[AlexCuse]

Richo -

Are you trying to order by the total (sum of Support and Admin) or just the sum of support? If you want to order by the total calls handled, you might try ordering by sum(1) desc.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Richo1980]

Alex,

Using your code I've now created 2 queries...One for Admin and one for Support...

On my Admin and Support pages I have 2 columns, one with the Tech's name and the other with the total calls they have closed. As I mentioned I have seperate pages for both the Admin and Support calls

What I am trying to do now is order both of these pages so that the person who has closed the most amount of calls is on top down to the person with the least amount of calls

Please let me know if this makes sense

Thanks

 

[AlexCuse]

Then, in each query you should use an order by clause like the following:

ORDER BY Sum(IIf([HPD Assigned]='Whatever',1,0)) DESC;

Replacing 'whatever' with whatever you are querying for of course.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom