SQL: DISTINCT and Count()

[ahmed B]

Hello every one,

I have a table of categories that have two columns product and categ , I used to have a simple query to select category :

"select distinct categ from categs"

Now I want to order categories first by initial then by most frequent
I tried :

"select distinct categ 
from categs order by mid(categ,1,1) , count(categs) desc"

it didn't work so I separated queries like :

"select categ , 
(select count(categ) from categs order by count(categ)) 
from categs order by mid(categ,1,1)"

this query did the order but still repeating categories.
iam not sure where to put DISTINCT , I tried the above query as a part of another one that does distinct but it didn't work , any ideas?

[C CSR]

First, retry your sort order like this:

ORDER BY TEST1234.TEST1 DESC, TEST1234.TEST2 DESC;

Put the ordertype after each field.

[C CSR]

Try using a "WHERE" clause instead of DISTINCT to filter your records. Its better practice.

[Mariostg]

Ahmed, what you need is GROUP BY statement along with COUNT aggregate function.

This should get you started.

SELECT categ, COUNT(categ) AS countCateg
FROM categs 
GROUP BY categ
ORDER by mid(categ,1,1)

[mmabdulkadir]

ahamed, here u had used in group by clause because where it is aggregate function is used that only. group by clause is what are columns are selected the table using select clause that all the columns are used in group by clause except the aggregate function columns.

[mmabdulkadir]

this is syntax for order by and distinct

select distinct columnname from tablename 
order by columnname asc/desc
group by columnname

[NeoPa]

mmabdulkadir:
this is syntax for order by and distinct

I'm afraid that aggregation (GROUP BY) and the DISTINCT predicate don't go together very well (for pretty obvious reasons if you think about it). If you want a count, then you certainly don't want DISTINCT.

Mario's solution illustrates exactly what is required (LEFT([X],1) may get the initial more straightforwardly - but that's nitpicking).

[Mariostg]

what is required (LEFT([X],1) may get the initial more straightforwardly - but that's nitpicking).

I was going to leave the ORDER BY statement, but I am sure the OP will figure that one out :-)

[NeoPa]

I'm glad you didn't, as it illustrates that you can sort by an item that is not included in the SELECT clause. Many don't realise that I find (because it's not so obvious when developing from the Access design view).