SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group By but still get number of rows

    Hi guys

    I am creating a shopping cart

    What I want to do is this

    Say this is my cart

    apple
    apple
    apple
    I want it to appear like this

    3 apple

    How can i group by and still get the number of rows?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT 
        productname
      , count(*) as quantity
    FROM products
    GROUP BY productname

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT COUNT(*) AS how_many
         , item_description
      FROM carts
     WHERE item_description LIKE '%apple%'
    GROUP
        BY item_description
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Here you go, this should do the trick...

    PHP Code:
    <?php

    //--> Our function
    function aggregateArray$aArray )
    {
        
    $aAggregate = array();
        foreach (
    $aArray as $sArrayItem)
        {
            
    $aAggregate$sArrayItem ]++;
        }
        return 
    $aAggregate;
    }

    //--> Our cart array
    $aCart = array('apple','apple','apple','apple','pear','orange','orange');

    //--> Lets process it.
    $aAggregatedArray aggregateArray($aCart);

    //--> Show some info.
    foreach ($aAggregatedArray as $sCartItem => $iCount)
    {
        echo 
    sprintf("You have %s %s(s) in your cart.<br />\r\n",$iCount,$sCartItem);
    }

    /* Outputs....
    You have 4 apple(s) in your cart.
    You have 1 pear(s) in your cart.
    You have 2 orange(s) in your cart.
    */

    ?>
    SilverB.

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Oh, am I the only who assumed he meant using PHP?

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    Oh, am I the only who assumed he meant using PHP?
    So far, yes

    But you might be right. I assumed MySQL because the OP talks about grouping by, but it IS the PHP forum, so who knows...


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
  •