#1
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,354
    Rep Power
    24

    Select, Group By, Add?


    I guess the main page of this module I'm working on is meerly a list of jobs, and expense totals for those jobs. I currently have them in my DB under 2 columns. (Category, Cost) Now, per job listing, (row on main list), I need to retrive its info from 1 table, then in the second table, match all rows w/ JobID of main job. Then with those rows, I need to group by Category, and retrieve total Cost of the items in each category.

    Would this be possible straight from MySQL? Or would I be best off running a PHP loop to fetch main job, fetch matching expense rows, group, and add the grouped expenses for me?
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,567
    Rep Power
    4287
    Originally Posted by Triple_Nothing
    Would this be possible straight from MySQL?
    sure, it's a simple join query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,354
    Rep Power
    24
    I know the JOINing of the 2 tables, but my loss is around the GROUPing and ADDing of the items needed. Sounds I be putting too much function/processing in my query. :-/
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,567
    Rep Power
    4287
    Originally Posted by Triple_Nothing
    Sounds I be putting too much function/processing in my query. :-/
    no, not at all
    Code:
    SELECT t1.id
         , t1.name
         , SUM(t2.cost) AS total_cost
      FROM table1 AS t1
    INNER
      JOIN table2 AS t2
        ON t2.t1id = t1.id
    GROUP
        BY t1.id
         , t1.name
    this is what database engines are good at
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,354
    Rep Power
    24
    Well, as per your response, that is all still wut I get. The thing is really the total. Say there are 3,000 rows in the table. I am listing Expense report 735. This should then SELECT all rows in the details table w/ ERID 735. It finds 17. They have 3 different Category IDs, so they get set into 3 GROUPs. I then need the items within those groups added up to give me a total cost for that category, and end up w/ Cat1 = $17.93, Cat2 = $47.26... :-/
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  10. #6
  11. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,943
    Rep Power
    4037
    You can use the WITH ROLLUP modifier to have mysql output an extra row for each level of grouping. The row would have one of the group fields set to NULL and the totals for all the sub groups.

    Code:
    SELECT t1.id
         , t2.category
         , SUM(t2.cost) AS total_cost
      FROM jobs AS t1
    INNER
      JOIN expenses AS t2
        ON t2.jobid = t1.id
    GROUP
        BY t1.id
         , t2.category
    WITH ROLLUP
    For example the query would return something like

    Code:
    id	category	total_cost
    -------------------------------
    1	a		100
    1	b		200
    1	c		300
    2	NULL		600
    2	a		10
    2	b		20
    2	c		30
    2	NULL		60
    3	a		1
    3	b		2
    3	c		3
    3	NULL		6
    NULL	NULL		666
    You'd have your code detect those NULL rows and use them as the total. Whether you want to do it this way, or just doing the adding yourself in PHP as you loop the results is up to you.

    http://sqlfiddle.com/#!2/772fe/2
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  12. #7
  13. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,354
    Rep Power
    24
    This is EXACTLY what I am looking for. Thank you guys so much! I've used MySQL for years, but always for more simple SELECT, UPDATE, INSERT queries. Never knew it could do so much... ^_^
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo