need help with query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

need help with query

Post by kreoton »

Hi, i have a table with catagories and other is with records, i whant to write a query whitch returns catagories with records numbers.
I have wroted query like this:

Code: Select all

SELECT C.cat_id,
		       C.cat_name,
		       C.cat_desc,
		       COUNT(R.rec_id)
		       FROM categories C, records R
		       WHERE R.cat_id = C.cat_id
		       GROUP BY (C.cat_id)
But this one returns only one catagory, how can i get all caegories?

P.S. Sorry for my English :roll:
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Where did you copy the query from? Why do you use a GROUP BY clause in your statement? What does it do?
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Post by kreoton »

timvw wrote:Where did you copy the query from? Why do you use a GROUP BY clause in your statement? What does it do?
I didnt copy this query, i use GROUP BY becase COUNT not working without it?

I whant get categorys whit number of records i.e.:
categories - number of records
SPORT - 6
FOOD - 5
DRINK - 8
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

maybe it's how you retrieve the records that only gives you one?
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Re: need help with query

Post by ody »

kreoton wrote:Hi, i have a table with catagories and other is with records, i whant to write a query whitch returns catagories with records numbers.
I have wroted query like this:

Code: Select all

SELECT C.cat_id,
		       C.cat_name,
		       C.cat_desc,
		       COUNT(R.rec_id)
		       FROM categories C, records R
		       WHERE R.cat_id = C.cat_id
		       GROUP BY (C.cat_id)
But this one returns only one catagory, how can i get all caegories?

P.S. Sorry for my English :roll:
Are you sure there is data for other catogories and records for them? if you have other catagories and no records you will need to left join to see them:

Code: Select all

SELECT C.cat_id, C.cat_name, C.cat_desc, COUNT(R.rec_id)
FROM categories as C left join records as R on R.cat_id = C.cat_id
GROUP BY (C.cat_id)
Post Reply