SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot doca's Avatar
    Join Date
    Dec 2004
    Location
    Vienna, AT
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    COUNT() returned from multiple tables?

    hello

    is there a way to get SUM of COUNT(*) AS user_id of two tables in one query?
    this code shows only table rtv ID : NAME but I need ID: NAME + COUNT SUMMARY

    PHP Code:
    $sql "SELECT blah.* FROM rtv AS blah LEFT JOIN (SELECT mes_id_fk, COUNT(*) AS user_id FROM vote_ip WHERE date> (NOW() - INTERVAL 2592000 SECOND) GROUP BY mes_id_fk) AS top_voted ON blah.id = top_voted.mes_id_fk ORDER BY top_voted.user_id DESC limit 5";
            
    $result mysql_query($sql) or die(mysql_error());
            while(
    $row mysql_fetch_array($result)){
            
            
    $list .= "".$row[id].": ".$row[naslov]." <br>";
        } 
            echo 
    $list

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select sum(columnName)
    from
    (
    select sum(columnName1) as columnName
    from
    table1
    union all
    select sum(columnName2) as columnName
    from
    table2
    )

  3. #3
    SitePoint Zealot doca's Avatar
    Join Date
    Dec 2004
    Location
    Vienna, AT
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's not what I need my code works great, just I do not know to get COUNT(*) AS user_id summed for each id

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    SELECT blah.*, top_voted.user_id

  5. #5
    SitePoint Zealot doca's Avatar
    Join Date
    Dec 2004
    Location
    Vienna, AT
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    SELECT blah.*, top_voted.user_id
    You're right Guido but That's the Problem
    I need top_voted.user_id in line below but nothing works (

    PHP Code:
    $list .= "".$row[id].": ".$row[naslov]." <br>"

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by doca View Post
    You're right Guido but That's the Problem
    I need top_voted.user_id in line below but nothing works (

    PHP Code:
    $list .= "".$row[id].": ".$row[naslov]." <br>"
    Try quoting the array keys, and concatenating the strings correctly:

    PHP Code:
    $list .= $row['id'] . ": " $row['naslov'] . "<br>"
    You can always try and use:
    PHP Code:
    var_dump($row) ; 
    to see what is being returned in $row.

    EDIT ps I am not addressing your entire problem, just responding to some syntax errors in your last posting.

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Kevin's answer is almost the correct one (DEPENDING on the actual desired result... i think you want the individual sums as well? Could be reading it wrong.), but perhaps explaining your tables a little bit more might help; why do you want the count of two seperate tables? There's probably a better way to design the DB/query to get what you want.

    Code MySQL:
    SELECT SUM(sub1.field1, sub2.field2),sub1.field1,sub2.field2 FROM 
    (SELECT COUNT(*) AS field1 FROM table1) AS sub1,
    (SELECT COUNT(*) AS field2 FROM table2) AS sub2;
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  8. #8
    SitePoint Zealot doca's Avatar
    Join Date
    Dec 2004
    Location
    Vienna, AT
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    Kevin's answer is almost the correct one (DEPENDING on the actual desired result... i think you want the individual sums as well? Could be reading it wrong.), but perhaps explaining your tables a little bit more might help; why do you want the count of two seperate tables? There's probably a better way to design the DB/query to get what you want.

    Code MySQL:
    SELECT SUM(sub1.field1, sub2.field2),sub1.field1,sub2.field2 FROM 
    (SELECT COUNT(*) AS field1 FROM table1) AS sub1,
    (SELECT COUNT(*) AS field2 FROM table2) AS sub2;

    OK StarLion

    I have one sql table that looks like this called "users":

    TABLE USERS

    Code:
    -------------------------------
     user_id    |  username
    -------------------------------
     1          |   tom
     2          |   john
     3          |   tim


    and another called "vote" that stores either upvotes or downvotes (downvotes deactivated).



    TABLE VOTE
    Code:
    ------------------------------
    id     |   user_id   |   up  |
    ------------------------------
    1      |  1	     |   Y   |
    2      |  1	     |   Y   |
    3      |  2	     |   Y   |
    4      |  2	     |   Y   |
    5      |  3	     |   Y   |

    how would I go about ordering vote by tim tom und johh by the number of (upvotes) ?

    I need something like this code below

    1. tom has 200 voted
    2. tim has 180 voted
    3. john has 178 voted

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Oh is that all? Much more simple then.

    Code mySQL:
    SELECT users.username,COUNT(vote.up) AS votes_received 
    FROM users 
    LEFT JOIN vote 
    ON users.user_id = vote.user_id AND vote.up = 'Y' 
    GROUP BY users.user_id 
    ORDER BY votes_received DESC;

    see, giving us all the information gets you better answers!
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    see, giving us all the information gets you better answers!
    ^ this

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

  11. #11
    SitePoint Zealot doca's Avatar
    Join Date
    Dec 2004
    Location
    Vienna, AT
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    trophy-icon.png

    This Code Work!!

    Big Thanks to StarLion


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
  •