Easy SQL question, I think.

Lee Grey

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Easy SQL question, I think.

I'm trying to select a result set that contains distinct names AND the
number of times each name occurs. I thought the following would work:

select distinct name, count(name) from tbl

But that's no good. Is there a way to do what I want in a single select?
Does it require some kind of subselect?

Thanks!
__________________________________________
FREE Windows 95/98/NT option trading software at
http://www.optioninsight.com. Option Insight spots the opportunities in an
option chain. Give it a try!

Also FREE: MultiBrowse for Stocks, a multi-paned browser that retrieves
your favorite webpages all at once for each ticker symbol you enter.

randy

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Easy SQL question, I think.

In Oracle it would be

select name,count(1) from table_name
group by name

Lee Gre

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Easy SQL question, I think.

That did it. Thanks! Can you explain the count(1) construct? Does the 1
refer to the first column in the list?

Quote

"randyr" <ran...@tradewinds-software.com> wrote in message

news:38aade3b@dnews...

Quote

> In Oracle it would be

> select name,count(1) from table_name
> group by name

Jim Elde

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Easy SQL question, I think.

Quote

>>Can you explain the

count(1) construct? Does the 1
refer to the first column in the list<<

Yes.

The statement could also have been written as

SELECT name, COUNT(name) FROM Table_Name
GROUP BY name

It's the GROUP BY that makes 'name' distinct in the SELECT list.

NB: 'name' can be a reserved word so be careful.

HTH,
Jim

Nare

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:Easy SQL question, I think.

Another nice piece of SQL answers the question: How many names appear more
than once?
This is useful on very large tables.

select name,count(name) from table_name
group by name
having count(name) > 1

Other Threads

1. easy question (I think)

2. Easy Question (I Think)

3. Easy question (I think!)

4. Complex SQL Question - I Think :-P

5. I think it's an SQL question?

6. 3 easy problem i think, Help Help

7. 3 easy problem i think Help Help

8. Tip: Easy Modal Forms (I think)

9. Easy, easy Delphi question

10. Easy Questions, Easy Answers...