sql sum question

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.

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.

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/