sql sum question

[trojnfn]

I need to produce a CF report only showing the top five suppliers, plus other information.

I count each supplier, with group by, then sum the count, with group by, to get the total occurrances of each supplier. My query/subquery is something like this :

select sum(supplierCount) as totalCount, supplier
from
(
select count(supplier) as suppliercount, supplier
from table
group by supplier
) as a
group by supplier
order by supplier

This appears to work and gives me the total occurrances of each supplier. I can then select the top 5. Howver, when I start including the other columns, the count/sum does not seem to work anymore and becomes 1 for each supplier. I even tried usign QofQ and that did not work.

How can I write the query to give me the total number of occurrances of each supplier, in addition to having the other columns on the report ?

[Newsgroup_User]

if you were using mysql as your db, your current query would have worked
just fine with any other columns you added - mysql does not require the
query to be grouped by ALL non-aggregate columns, one is enough.

in your case, i think you will need to have 2 queries and then combine
then with a QoQ.

something like (untested):

<!--- get top 5 suppliers with unique supplier id --->
<cfquery name="top5suppliers" ...>
SELECT TOP 5 COUNT(supplier_id) AS supplierCount, supplier_id
FROM table
GROUP BY supplier_id
ORDER BY COUNT(supplier_id) DESC
</cfquery>

<!--- store top 5 supplier ids in a list --->
<cfset supplier_id_list = valuelist(top5suppliers.supplier_id)>

<!--- get all other data for top 5 suppliers --->
<cfquery name="top5suppliersData" ...>
SELECT supplier_id, ....
FROM suppliers_table
WHERE supplier_id IN (#supplier_id_list#)
</cfquery>

<!--- combine data using QoQ --->
<cfquery name="suppliersFullData" dbtype="query">
SELECT top5suppliers.supplierCount, top5suppliersData.*
FROM top5suppliers, top5suppliersData
WHERE top5suppliers.supplier_id = top5suppliersData.supplier_id
ORDER BY supplierCount DESC
</cfquery>



Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Dan_Bracuk]

You've already shown that you can select from a subquery. You just have to combine that subquery with some real tables and your are in business.

[Kronin555]

select * from table join (
SELECT TOP 5 COUNT(supplier_id) AS supplierCount, supplier_id
FROM table
GROUP BY supplier_id
ORDER BY COUNT(supplier_id) DESC
) as a on (table.supplier_id = a.supplier_id)

This will give you everything from "table", matching on supplier_id, and also include the count you generated. I'm just using Azadi's table as a join table.