SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 29 of 29

Thread: PHP SQL count

  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by iansteeze View Post
    this gives me an error
    sorry, my Microsoft® CrystalBall© is down at the moment, you're going to have to tell me what the error was

    any anyhow, you should be doing it this way for the sake of performance:
    Code:
    SELECT canada
         , COUNT(*) AS cnt
      FROM phpbb_buysell
     WHERE canada IN ('canada','usa','international')
    GROUP
        BY canada
    and if those are the only 3 values for the canada column, then you can drop the WHERE clause

    yes, it gives you three rows, but reformatting them into one line is a job for the front end application language, not SQL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, my Microsoft® CrystalBall© is down at the moment, you're going to have to tell me what the error was

    any anyhow, you should be doing it this way for the sake of performance:
    Code:
    SELECT canada
         , COUNT(*) AS cnt
      FROM phpbb_buysell
     WHERE canada IN ('canada','usa','international')
    GROUP
        BY canada
    and if those are the only 3 values for the canada column, then you can drop the WHERE clause

    yes, it gives you three rows, but reformatting them into one line is a job for the front end application language, not SQL


    this is the error

    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int' at line 16 [1064]
    
    SQL
    
    select * from ( select count(*) as canada_cnt from phpbb_buysell where canada ="canada" ) as canada cross join ( select count(*) as usa_cnt from phpbb_buysell where canada ="usa" )as usa cross join ( select count(*) as int_cnt from phpbb_buysell where canada ="international" )as int
    
    BACKTRACE

  3. #28
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aaah it must have been because i was using "int"

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    INT is a reserved word

    change the last line to ... ) AS intq
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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