Page 1 of 1

need help with query

Posted: Sat Mar 11, 2006 5:38 pm
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:

Posted: Sat Mar 11, 2006 6:11 pm
by timvw
Where did you copy the query from? Why do you use a GROUP BY clause in your statement? What does it do?

Posted: Sat Mar 11, 2006 6:18 pm
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

Posted: Sat Mar 11, 2006 6:25 pm
by feyd
maybe it's how you retrieve the records that only gives you one?

Re: need help with query

Posted: Sun Mar 12, 2006 4:55 am
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)