Group by , ordering

rbuser

Is it not possible for a report (end-user) to order by the number of
"counts"? It is not enough to show the "count" per group; they need to
be in order of "count." Of course, I can group by Name and get Sales
counts, but not in order of "count." The report works if I edit the SQL,
but I need to have autosearch fields for the SaleDate parameter, which
means I can't edit the SQL:

SELECT COUNT(SALES.SALE_ID), NAMES.NAME FROM SALES JOIN NAMES ON
(SALES.NAME_ID=NAMES.NAME_ID) WHERE SALES.SALEDATE > '1/1/2003' ORDER BY
1 DESC

Output:

Diego Rivera 45
Pablo Cassals 43
Ricardo Muzzi 32



Dennis McFall

nicocizik

Dennis,

It is possible to set the 'Count' SQL function using DADE in the 'calc' tab
of the Query Designer. You will have to select the 'Expression' option and
enter the function in manually. Then you must be sure to group by all other
selected fields. The SQL will then look something like this...

SELECT Count(Sales.Sale_ID), Names.Name
FROM Sales
JOIN Names ON (Sales.Name_ID = Names.Name_ID)
WHERE Sales.SaleDate > '1/1/2003'
GROUP BY Names.Name
ORDER BY 1 DESC

Below is a link to a similar example of what I described above and should
help get you on the right track.

http://www.digital-metaphors.com/tips/CountFunctionWithParameter.zip

--

rbuser

Nico Cizik (Digital Metaphors) wrote:


Excellent!! Thank you....

dm