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

    Join Date
    Nov 2010
    Posts
    9
    Rep Power
    0

    Splitting data & getting datacount


    i have a column (summary) with the data as

    SUMMARY

    1-AB-XY
    2-OP-AB
    1-AB-XY
    1-MN-ZZ-AB
    1-OP-AB
    2-AB-XY
    1-MN-ZZ-AB
    1-OP-AB
    1-MN-ZZ-AB

    2) i need SQL query to get the data and its count of the column data like


    1-AB - 7(count)
    1-XY - 2(count)
    2-OP - 1(count)
    1-MN - 3(count)
    1-ZZ - 3(count)
    1-OP - 2(count)
    2-AB - 2 (count)
    2-XY - 1 (count)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,234
    Rep Power
    4279
    Code:
    SELECT SUBSTRING(summary FROM 1 FOR 4) AS left4
         , COUNT(*)
      FROM daTable
    GROUP
        BY left4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo