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

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

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)