By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

SQL: DISTINCT and Count()

P: 1
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 :
Expand|Select|Wrap|Line Numbers
  1. "select distinct categ from categs"
Now I want to order categories first by initial then by most frequent
I tried :
Expand|Select|Wrap|Line Numbers
  1. "select distinct categ 
  2. from categs order by mid(categ,1,1) , count(categs) desc"
it didn't work so I separated queries like :
Expand|Select|Wrap|Line Numbers
  1. "select categ , 
  2. (select count(categ) from categs order by count(categ)) 
  3. 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?
Jan 17 '12 #1

✓ answered by Mariostg

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

This should get you started.
Expand|Select|Wrap|Line Numbers
  1. SELECT categ, COUNT(categ) AS countCateg
  2. FROM categs 
  3. GROUP BY categ
  4. ORDER by mid(categ,1,1)

Share this Question
Share on Google+
8 Replies


100+
P: 144
First, retry your sort order like this:
Expand|Select|Wrap|Line Numbers
  1. ORDER BY TEST1234.TEST1 DESC, TEST1234.TEST2 DESC;
Put the ordertype after each field.
Jan 17 '12 #2

100+
P: 144
Try using a "WHERE" clause instead of DISTINCT to filter your records. Its better practice.
Jan 17 '12 #3

100+
P: 332
Ahmed, what you need is GROUP BY statement along with COUNT aggregate function.

This should get you started.
Expand|Select|Wrap|Line Numbers
  1. SELECT categ, COUNT(categ) AS countCateg
  2. FROM categs 
  3. GROUP BY categ
  4. ORDER by mid(categ,1,1)
Jan 17 '12 #4

P: 7
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.
Jan 17 '12 #5

P: 7
this is syntax for order by and distinct
Expand|Select|Wrap|Line Numbers
  1. select distinct columnname from tablename 
  2. order by columnname asc/desc
  3. group by columnname
Jan 17 '12 #6

NeoPa
Expert Mod 15k+
P: 31,709
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).
Jan 17 '12 #7

100+
P: 332
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 :-)
Jan 17 '12 #8

NeoPa
Expert Mod 15k+
P: 31,709
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).
Jan 17 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.