#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7

    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7
    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7
    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - 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,958
    Rep Power
    4035
    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
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7
    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him

IMN logo majestic logo threadwatch logo seochat tools logo