counting number of RFQ's each person has

[jamie61880]

Hi. Hopefully this isn't too difficult. I have a field for RFQ Leader Initials. The table has lots of RFQ's in it and each RFQ has an RFQ Leader assigned to it. I would like to count how many RFQ's each person has assigned to them. I can get a total count of all the RFQ's that everyone in the database has by using this: #RFQLeaderSearch.RecordCount#, but I can't narrow it down to see how many each person has. On the first page, I have a checkbox list that a user can choose who they want to search for. On the action page, I just have a small table that outputs the RFQ Leader and the number of RFQ's each person has. Does anyone know how I can get this to work? I have attached the action page code. Can someone please help me? Thank you.
Andy

[Dan_Bracuk]

Are you familiar with selecting aggregates such as count using nothing but sql? If so, why do you not consider it applicable to this situation?

If not, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

[jamie61880]

Dan,
No, I'm not familiar with the count function. Is SQL the only way to make this work?

Andy

[Dan_Bracuk]

quote:

---

Originally posted by: jamie61880
Dan,
No, I'm not familiar with the count function. Is SQL the only way to make this work?

Andy

---

There is never just one way to do anything. But using sql count() is the simplest and most efficient.

[Kapitaine]

Hi jamie61880,

SQL has a lot of functions to do various types of things. The one you probably need to be investigating is the COUNT() function. e.g.

SELECT COUNT(*)
FROM someTable

A more complex example would be something like:

SELECT COUNT(*)
AS numOfEmployees
FROM employees
WHERE salary > 25000

In any case, what I'm trying to say is that you can get calculations and perform sums on database data without having to do it in ColdFusion. The advantage here is that it is a LOT quicker, because you're calculating things the database was designed to do.

Its also better this way to keep calculations simpler. If you were using CFC's (which I see you aren't) you could create specific functions like getItemCount(id=5) which could return the count from a query which uses this COUNT() function.

Check out this page which is very helpful:
http://www.techonthenet.com/sql/count.php

Good luck,
Mikey.

[Kapitaine]

Hi jamie61880,

SQL has a lot of functions to do various types of things. The one you probably need to be investigating is the COUNT() function. e.g.

SELECT COUNT(*)
FROM someTable

A more complex example would be something like:

SELECT COUNT(*)
AS numOfEmployees
FROM employees
WHERE salary > 25000

In any case, what I'm trying to say is that you can get calculations and perform sums on database data without having to do it in ColdFusion. The advantage here is that it is a LOT quicker, because you're calculating things the database was designed to do.

Its also better this way to keep calculations simpler. If you were using CFC's (which I see you aren't) you could create specific functions like getItemCount(id=5) which could return the count from a query which uses this COUNT() function.

Check out this page which is very helpful:
http://www.techonthenet.com/sql/count.php

Good luck,
Mikey.

[jamie61880]

Mikey,
Thank you. That web site worked. Here's what I did below. Is there anyway to display the initials that have zero for a count? Thanks again.

Andy