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

    Join Date
    Nov 2013
    Posts
    1
    Rep Power
    0

    Mysql counting number of occurrence rows different tables for same user


    i have 2 different tables which user_id as common field.

    how to write query to count total occurrence of each user in both tables.

    say for example. there are 2 & 3 records for a user in two tables respectively. i need user id and total records (i.e) 5
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Code:
    SELECT userid
         , SUM(subtotal) AS total
      FROM ( SELECT userid
                  , COUNT(*) AS subtotal
               FROM table1
             GROUP
                 BY userid
             UNION ALL
             SELECT userid
                  , COUNT(*)
               FROM table2
             GROUP
                 BY userid ) AS u
    GROUP
        BY userid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo