SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I do this query?

    Code MySQL:
    			ID      Name
    			3	Action
    			13	Action
    			16	Action
    			24	Action
    			26	Action
    			32	Action
    			35	Action
    			40	Action
    			44	Action
    			76	Action
    			95	Action
    			112	Adventure
    			114	Adventure
    			127	Adventure
    			128	Adventure
    			135	Adventure
    			136	Adventure

    I want to display on page:

    Action (How many results Action has)
    Adventure (How many results Adventure has)

    So if Action had 15 results, adventure had 6, it'd show like this:

    Action (15)
    Adventure (6)

    It only shows the NAME once, but I also want it to show how many of that name there are in the table.

    How do I do a query like this?

    Regards
    :-)

  2. #2
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, I worked it out, forgot about DISTINCT !
    :-)

  3. #3
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's hard to tell from the code snippet, but I'm assuming the numbers should actually be under the ID title and that the Name column has multiple "Adventures" and multiple "Actions" like this

    ID Name
    3 Action
    4 Action
    5 Action
    6 Adventure
    7 Adventure
    8 Adventure

    Correct?

    There are two ways that I know of to be able to count records with SQL, both involve the COUNT() function:

    One is to count the Name field each time when the Name has the value of 'Action' or 'Adventure' individually.
    Code SQL:
    SELECT COUNT(Name) FROM tblName WHERE Name='Action';
    SELECT COUNT(Name) FROM tblName WHERE Name='Adventure';

    The other is to use COUNT(DISTINCT columnName):
    Code SQL:
    SELECT COUNT(DISTINCT ID) FROM tblName WHERE Name='Action';
    SELECT COUNT(DISTINCT ID) FROM tblName WHERE Name='Adventure';

    Both should get you the numbers you're looking for with separate queries.

    If you're looking for one query to do both and then are writing the values out with ASP:
    Code ASP:
    <%
    rs.open "SELECT ID, Name FROM tblName;", conn
     
    dim act, adv
     
    act = 0
    adv = 0
     
    do while not rs.EOF
      if rs.Fields("Name")="Action" then
        act = act + 1
      end if
     
      if rs.Fields("Name")="Adventure" then
        adv = adv + 1
      end if
     
    rs.MoveNext
    loop
    %>
     
    Action = <%=act%>
    Adventure = <%=adv%>
     
    <% rs.close %>

    If you want to use PHP (I believe this is how to do it):
    PHP Code:
    <?php
    $sqlAdv 
    mysql_query("SELECT ID FROM table WHERE Name='Adventure'");
    $sqlAct mysql_query("SELECT ID FROM table WHERE Name='Action'");

    $countAdv mysql_num_rows($sqlAdv);
    $countAct mysql_num_rows($sqlAct);
    ?>

    Action = <?=$act?>
    Adventure = <?=$adv?>
    These are my best guesses. I am personally using the ASP version I gave you. The PHP process is strictly an informed guess-timate.

    Happy coding.
    Follow me: @josh_max

  4. #4
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Must have just missed your reply. :P
    Follow me: @josh_max

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    separate queries? no, there's a much easier way
    Code:
    SELECT Name
         , COUNT(*) AS ids
      FROM daTable
    GROUP
        BY Name
    luverly, innit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still learning.

    Never knew you could do that. Should come in handy!
    Follow me: @josh_max


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
  •