11 replies
Alright I have a table that houses bills per car.id so in this table is a list of bills that assocaited by the car.id

so there may be 20 different entries for the same car.id how can I query the table to return all the bills per car.id ADDED up and spit out ONE total

I'm using this query right now

SELECT * FROM `cars` JOIN `bills` ON cars.id=bills.car_id WHERE cars.make="$userchoice" ORDER BY cars.year DESC

this is returning about 20 copies of the same vehicle with each bill how can I get ONE total in the cost colum and one copy of the vehicle
#php #query #question
  • Profile picture of the author goosefrabah
    Originally Posted by solidsoul View Post

    Alright I have a table that houses bills per car.id so in this table is a list of bills that assocaited by the car.id

    so there may be 20 different entries for the same car.id how can I query the table to return all the bills per car.id ADDED up and spit out ONE total

    I'm using this query right now

    SELECT * FROM `cars` JOIN `bills` ON cars.id=bills.car_id WHERE cars.make="" ORDER BY cars.year DESC

    this is returning about 20 copies of the same vehicle with each bill how can I get ONE total in the cost colum and one copy of the vehicle
    I'm not sure of the field you're adding but


    SELECT SUM(sum_field_to_be_added),cars_id,etc FROM `cars` JOIN `bills` ON cars.id=bills.car_id WHERE cars.make="$userchoice" GROUP BY cars.id ORDER BY cars.year DESC

    MySQL Tutorial - Sum has a good example
    {{ DiscussionBoard.errors[5922283].message }}
  • Profile picture of the author Earnie Boyd
    Or you may need to use a DISTINCT specifier if you don't want to same the same identical cost. See Select UNIQUE or DISTINCT MySQL/PHP Queries | NewSourceMedia Blog for an example.
    Signature
    {{ DiscussionBoard.errors[5922474].message }}
  • Profile picture of the author solidsoul
    what if I was to set an array first off the bills then spit them out according to the car.id or am I thinking this too far
    {{ DiscussionBoard.errors[5922541].message }}
  • Profile picture of the author SteveJohnson
    Use GROUP BY and WITH ROLLUP, will do exactly what you're wanting.

    MySQL :: MySQL 5.0 Reference Manual :: 11.15.2 GROUP BY Modifiers
    Signature

    The 2nd Amendment, 1789 - The Original Homeland Security.

    Gun control means never having to say, "I missed you."

    {{ DiscussionBoard.errors[5923318].message }}
  • Profile picture of the author solidsoul
    ok so running this in mysql phpadmin SELECT car_id, SUM(cost) FROM bills GROUP BY car_id

    returns the bills added up as desired - associated with the car_id

    PROBLEM now is I need to spit this data out with the year, make, model, VIN, kms, color and TOTAL COST

    the rest this data is stored in cars while bills obviously housing the bills ... tried a few variations of adding a join statment in with that but from what I can see bills associated the ID with car_id and cars is just id

    so saying something like group by id isn't a true statement I'm almost ready to give up and just make seperate queries grab what I need and combine the data??
    {{ DiscussionBoard.errors[5925500].message }}
  • Profile picture of the author SteveJohnson
    Code:
    SELECT c.*, b.car_id, SUM(b.cost)
    FROM cars c, bills b
    WHERE c.id=b.car_id
    GROUP BY b.car_id WITH ROLLUP
    Signature

    The 2nd Amendment, 1789 - The Original Homeland Security.

    Gun control means never having to say, "I missed you."

    {{ DiscussionBoard.errors[5926114].message }}
  • {{ DiscussionBoard.errors[5929678].message }}
  • Profile picture of the author solidsoul
    Steve would this work to add the fact that I want to limit the results to $userchoice regarding MAKE

    SELECT cars.*, bills.car_id, SUM(bills.cost) FROM cars, bills WHERE cars.id=bills.car_id && cars.make=honda GROUP BY cars.year ORDER BY cars.year DESC

    this in return what I want is too have the user select a make - honda, kia, ext ext and list out all the vehicles we have that meet that make and spit out the data.. appreciate all the help steve

    PS: I tried this query as I have it and no it dont work says colum honda don't exsit
    {{ DiscussionBoard.errors[5929802].message }}
  • Profile picture of the author solidsoul
    for the record I even tried an AND to ask for only those records that are cars.make=`$userchoice` that still returns an invalid colum I ensure cars.make is there
    {{ DiscussionBoard.errors[5929841].message }}
  • Profile picture of the author Earnie Boyd
    Surround the text data with ' not `. Surround the tables and columns with ` and not '.

    Here is your query fully delimited as MySQL likes it. Fully delimiting the tables and columns ensures that if your table or column is a SQL reserved word that query still will work.

    SELECT `cars`.*, `bills`.`car_id`, SUM(`bills`.`cost`) FROM `cars`, `bills` WHERE `cars`.`id`=`bills`.`car_id` && `cars`.`make`='honda' GROUP BY `cars`.`year` ORDER BY `cars`.`year` DESC

    I would also suggest an alias name for your SUM as in

    SELECT `cars`.*, `bills`.`car_id`, SUM(`bills`.`cost`) AS "cost" FROM `cars`, `bills` WHERE `cars`.`id`=`bills`.`car_id` && `cars`.`make`='honda' GROUP BY `cars`.`year` ORDER BY `cars`.`year` DESC

    Notice the " which is even another delimiter.
    Signature
    {{ DiscussionBoard.errors[5930032].message }}
  • Profile picture of the author solidsoul
    Got it made a few changes and works like a dream.... THANK YOU soo much for your efford in helping me!

    heres what worked Removed the "" from AS "cost" to `cost` I was getting error other way..

    here full query that worked..

    SELECT `cars`.*, `bills`.`car_id`, SUM(`bills`.`cost`) AS `cost` FROM `cars`, `bills` WHERE `cars`.`id`=`bills`.`car_id` && `cars`.`make`='$userchoice' GROUP BY `cars`.`id` ORDER BY `cars`.`year` DESC

    So what worked was chaning also the GROUP BY to cars.year gave me one copy of all vehicle with a SUM total cost...

    again thanks soo much
    {{ DiscussionBoard.errors[5930149].message }}

Trending Topics