...

View Full Version : Syntax Help



StupidRalph
01-30-2007, 06:52 PM
I'm trying to find out the right syntax I would need to add in the SUM(). I'm trying to be able to obtain the TOTAL COUNT of records returned. I currently having it grouping by the business ID so the count may look like this


Count Score
1 5
4 3
2 2

I'm trying to find the total count which would be 7. I can do this by adding additional PHP code but I'd prefer to do this within the SQL as I feel its only appropiate. Any help is appreciated. A simple link will suffice as well. Thanks



SELECT COUNT(tbl_biz.bizID),
MATCH(bizName,keywords)
AGAINST ('string') AS score
FROM tbl_biz
LEFT OUTER JOIN tbl_addy ON tbl_addy.bizID = tbl_biz.bizID
WHERE MATCH(bizName,keywords)
AGAINST ('string')
GROUP BY tbl_biz.bizID;


I really HATE to post this but i can't figure it out.

CFMaBiSmAd
01-30-2007, 07:09 PM
It sounds like you want the result to contain the rows you show and a final row that contains the total of the count?

If so, check out the WITH ROLLUP modifier for the GROUP BY clause.

StupidRalph
01-30-2007, 07:11 PM
Yes. That is exactly what I am looking to do. I will search for that now. Thanx.

StupidRalph
01-30-2007, 07:34 PM
This works for me as it returns the total count result. My problem now is I don't know how to return just the rollup result to PHP.

Okay well actually I really didn't want to have to use the GROUP BY clause. But I received an error that told me that I had to use the GROUP BY clause. I was assuming it was complaining b/c I am using MATCH() and AGAINST() is why it wanted me to use GROUP BY. If I could simply return the total number of rows returned (I am not concerned with the individual grouped counts).

I do have a field with common database value that is in all rows that i could Group By but I know that is poor implentation as the value for that field will eventually have other values.

guelphdad
01-30-2007, 10:33 PM
well your explantion was not clear at all. you had a group by and indicated that is what you needed as well as the full total.

all you are looking for is a count. Leave the GROUP BY off entirely and then give an alias to your count

SELECT COUNT(tbl_biz.bizID) as yourtotal
and then just refer to yourtotal in your php code as you would any other column name.

StupidRalph
01-30-2007, 10:44 PM
I'm sorry GuelphDad.

The problem was that it was complaining about not having the GROUP BY clause. I origninally did not have the GROUP BY clause until I received an error. So I remedied this by deleting the following code colored in red.



SELECT COUNT(tbl_biz.bizID),

MATCH(bizName,keywords)
AGAINST ('string') AS score

FROM tbl_biz
LEFT OUTER JOIN tbl_addy ON tbl_addy.bizID = tbl_biz.bizID
WHERE MATCH(bizName,keywords)
AGAINST ('string')

GROUP BY tbl_biz.bizID;



which makes it look like this

SELECT COUNT(tbl_biz.bizID)AS totalcount
FROM tbl_biz
LEFT OUTER JOIN tbl_addy ON tbl_addy.bizID = tbl_biz.bizID
WHERE MATCH(bizName,keywords)
AGAINST ('string');

I've never used MATCH and AGAINST and didn't know.

guelphdad
01-31-2007, 02:36 PM
note that you appear to want to match items in both tables. you should use an INNER and not a LEFT OUTER join. An inner join returns matched records only and an outer join returns results from the left table even when there is no match in the right table. this might affect your count.

StupidRalph
01-31-2007, 03:14 PM
wow...I guess I should go back and re-read your 'joins basic' tutorial again. :) thanks



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum