Thread: Count items

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    4
    Rep Power
    0

    Count items


    Hi
    i have one table Objects with:

    IdObject
    FromMember int
    TypeObject char(1) <==== ( 'A', 'B', 'C', 'D')

    i need to know how many objecs are from each member, like this

    FromMember Type_A Type_B Type_C Type_D
    xxxxxxxxx count() count() count() count()

    i have tried
    select frommember, count(TypeObject) from objects group by typeobject...


    but dont work

    Any help?

    Thanks

    Marco
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Code:
    SELECT FromMember
         , COUNT(CASE WHEN TypeObject = 'A'
                      THEN TypeObject
                      ELSE NULL END ) AS Type_A
         , COUNT(CASE WHEN TypeObject = 'B'
                      THEN TypeObject
                      ELSE NULL END ) AS Type_B
         , COUNT(CASE WHEN TypeObject = 'C'
                      THEN TypeObject
                      ELSE NULL END ) AS Type_C
         , COUNT(CASE WHEN TypeObject = 'D'
                      THEN TypeObject
                      ELSE NULL END ) AS Type_D
      FROM Objects
    GROUP
        BY FromMember
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo