Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8

Thread: Syntax Help

  1. #1
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts

    Syntax Help

    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
    Code:
    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

    Code:
    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.
    Last edited by StupidRalph; 01-30-2007 at 07:12 PM.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,044
    Thanks
    2
    Thanked 316 Times in 308 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Yes. That is exactly what I am looking to do. I will search for that now. Thanx.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #4
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts

    So Close

    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.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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
    Code:
    SELECT COUNT(tbl_biz.bizID) as yourtotal
    and then just refer to yourtotal in your php code as you would any other column name.

  • #6
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    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.

    Code:
    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
    Code:
    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.
    Last edited by StupidRalph; 01-30-2007 at 10:49 PM.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #8
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    wow...I guess I should go back and re-read your 'joins basic' tutorial again. thanks
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •