SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting SUM from Joined Table

    Greetings,

    I've got a users table and I'm trying to output all users but corresponding to each user I need to get the SUM of distance from the mile_trac table for each user.

    IE: User First, User Last, Distance Walked

    This obviously isn't a query but might show the logic of what I'm going for:

    Code:
    SELECT users.first_name, users.last_name, (mile_trac.SUM(distance) WHERE mile_trac.user_id = users.id) FROM users, mile_trac
    I'm pretty unclear about how to do query between 2 tables with SUM(distance)

    Code:
    CREATE TABLE `mile_trac` (
      `id` int(11) NOT NULL auto_increment,
      `user_id` int(11) default NULL,
      `date` date default NULL,
      `duration` varchar(255) default NULL,
      `location` varchar(255) default NULL,
      `notes` varchar(255) default NULL,
      `distance` float default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    Code:
    CREATE TABLE `users` (
      `id` int(11) NOT NULL auto_increment,
      `first_name` varchar(255) default NULL,
      `last_name` varchar(255) default NULL,
      `address` varchar(255) default NULL,
      `city` varchar(255) default NULL,
      `state` varchar(255) default NULL,
      `zip_code` varchar(255) default NULL,
      `cell_phone` varchar(255) default NULL,
      `reminder_format` varchar(255) default NULL,
      `email` varchar(255) default NULL,
      `password` varchar(255) NOT NULL default '',
      `walk_regular` varchar(255) default NULL,
      `metro_park` varchar(255) default NULL,
      `yard_sign` varchar(255) default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    Thanks for any help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT users.first_name
         , users.last_name
         , SUM(mile_trac.distance) AS total_distance
      FROM users
    INNER
      JOIN mile_trac
        ON mile_trac.user_id = users.id
    GROUP
        BY users.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •