Count Query

[bobmunkhouse]

I have a table holding a list of names. There are currently 4 names, and they are each held a number of times.
(e.g. bob, jim, joe, paul, paul, joe, jim, jim, jim)

I need a query to display each name, with the number of times it appears in the table next to it
(e.g.
BOb 1
Jim 4
Joe 2
Paul 2
)

I have tried to achive this with a COUNT query. The problem is, if one name does not appear in the table I need the query to display a 0 next to the name, rather than the name not appearing at all.

Please help.

p.s. if this problem can be achieved using a report instead, that would be fine, thankyou

 

[r937]

with only one table? if the name does not appear in the table, how do we know what it is? should we show Fred? JimBob? Beelzebub?

there's gotta be some other table with a list of names

rudy

 

[MichaelRed]

other than -as already noted- look up crosstab query in whatever reference material you use. or -perhaps- just see if ye olde query wiz is sufficient

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bobmunkhouse]

[reply to rudy]:

ok, that's fine

I have another query holding all the names...this is how we know what all the all the names are... can my problem now be solved??

and how

BObMunkhouse

 

[r937]

yup, now it's a left outer join

[tt]select othertable.name
, count(tablewithdupes.name) as names
from othertable
left outer
join tablewithdupes
on othertable.name = tablewithdupes.name[/tt]

now you may know that in a left outer join, rows from the left table that have no match in the right table are included, with the values of the columns that come from the right table set to null

that is why count(*) is no good, in a left join there will always be at least one row, whether there was a match or not

but since aggregate functions ignore nulls, if you count the values of a right column, you get the correct answer


rudy

 

[bobmunkhouse]

cheers rudy,
I appreciate the help, but for the benefit of some less experienced users who don't have a scooby about what you just said, could you use Lamens terms.

much appreciated

Bob Munkhouse

 

[r937]

well, i'm sorry, but LEFT OUTER JOIN is laymens' terms

may i suggest

http://www.w3schools.com/sql/sql_join.asp

if you look at the LEFT JOIN example,

[tt] Name Product
Hansen, Ola Printer
Svendson, Tove
Svendson, Stephen Table
Svendson, Stephen Chair
Pettersen, Kari [/tt]

if you now do a GROUP BY on the name and select COUNT(*), you will get

[tt] Name COUNT(*)
Hansen, Ola 1
Svendson, Tove 1
Svendson, Stephen 2
Pettersen, Kari 1[/tt]

however, if you use COUNT(Product), you get

[tt] Name COUNT(Product)
Hansen, Ola 1
Svendson, Tove 0
Svendson, Stephen 2
Pettersen, Kari 0[/tt]


clearer?

 

[bobmunkhouse]

cheers for that website, it's helping loads