473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Sum from multiple id's

137 100+
Hello,
I need mysql to sum values from multiple values.
In my DB I have an auto increment primary key id, total_price, tax_price and a lot more.
Now I want to sum the total from (for example) the record id's: 1, 4, 5, 6, 8, 10.
How can I do this easily?

Expand|Select|Wrap|Line Numbers
  1. SELECT id, SUM( total_price) AS Total, SUM( tax_price) AS Tax 
  2. FROM workorder WHERE id = ..... ??
Thanks!
Cheers,
Paul
Jan 13 '10 #1
5 2337
dgreenhouse
250 Expert 100+
You should drop the id from the selection.

Expand|Select|Wrap|Line Numbers
  1. -- Should be just this
  2. SELECT
  3.        Sum(total_price) AS total,
  4.        Sum(tax_price)   AS tax
  5. FROM   workorder
  6. WHERE  id IN (1,4,5,6,8,10);
  7.  
  8. -- Output
  9. Total       Tax
  10. 68.00     34.00
  11.  
  12. -- Using with a UNION to show all the id's requested 
  13.  
  14. SELECT ''               AS id,
  15.        Sum(total_price) AS total,
  16.        Sum(tax_price)   AS tax
  17. FROM   workorder
  18. WHERE  id IN (1,4,5,6,8,10)
  19. UNION
  20. SELECT id,
  21.        total_price,
  22.        tax_price
  23. FROM   workorder
  24. WHERE  id IN (1,4,5,6,8,10) 
  25.  
  26. -- Output
  27. id     Total       Tax
  28.        68.00     34.00
  29.  1      2.00      1.00
  30.  4      8.00      4.00
  31.  5     10.00      5.00
  32.  6     12.00      6.00
  33.  8     16.00      8.00
  34. 10     20.00     10.00
  35.  
Jan 13 '10 #2
djpaul
137 100+
Wow, that's more then good!
I can use the next query you wrote for the other part where i have to print the hole workorder.
Finally i'm done with the design of the report.

Thanks a lot!

Greetz,
Paul
Jan 14 '10 #3
dgreenhouse
250 Expert 100+
Glad it help you out! :-)
Jan 14 '10 #4
Atli
5,058 Expert 4TB
Hey.

Just to put this out there...

You can also accomplish the same thing the UNION query does by using the GROUP BY ... WITH ROLLUP syntax.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     `id`,
  3.     SUM(`total_price` ) AS `total` , 
  4.     SUM(`tax_price` ) AS `tax`
  5. FROM `workorder`
  6. WHERE 
  7.     `id` IN (1, 4, 5, 6, 8, 10)
  8. GROUP BY `id`
  9.     WITH ROLLUP;
Expand|Select|Wrap|Line Numbers
  1. +------+-------+-------+
  2. | id   | total | tax   |
  3. +------+-------+-------+
  4. |    1 |  2.00 |  1.00 | 
  5. |    4 |  8.00 |  4.00 | 
  6. |    5 |  4.00 |  2.00 | 
  7. |    6 |  8.00 |  4.00 | 
  8. |    8 | 16.00 |  8.00 | 
  9. |   10 |  8.00 |  4.00 | 
  10. | NULL | 46.00 | 23.00 | 
  11. +------+-------+-------+
Jan 14 '10 #5
dgreenhouse
250 Expert 100+
Rollup's the best way...

And it'll allow you to do more advanced querying/reporting; as you can see by Atli's post.
Jan 14 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

32
by: Will Hartung | last post by:
Can someone clarify that multiple classes in the "class" attribute are ok and "legal" and not some fluke? So, I can do: ..pink {color: pink} ..bold {font-weight: bold} ..medium {font-size:...
7
by: Drew | last post by:
I have a db table like the following, UID, int auto-increment RegNo Person Relation YearsKnown Now here is some sample data from this table,
3
by: Nathan Sokalski | last post by:
When I view any page in my application a second time, I recieve the following error: System.Web.TraceContext.AddNewControl(String id, String parentId, String type, Int32 viewStateSize) +313...
9
by: Graham | last post by:
I have been having some fun learning and using the new Controls and methods in .Net 2.0 which will make my life in the future easier and faster. Specifically the new databinding practises and...
18
by: Gleep | last post by:
I've searched google intensely on this topic and it seems noone really knows how to approch this. The goal I don't want clients to give out their usernames and passwords to friends, since the site...
2
by: murraymiken | last post by:
I'm looking to have multiple multiple-select-boxes on a page. But I can only get the contents from the last selected value within a box, via PHP. I've tried numerous methods. What am I doing...
1
by: abhishekhs | last post by:
Hi all I have more than one multiple select lists in a page. Something like this <tr> <td> <select NAME="StrainList" ID="StrainList" SIZE="5" multiple="multiple" style="width: 150px"> <?...
92
by: bonneylake | last post by:
Hey Everyone, Well i was hoping someone could explain the best way i could go about this. i have a few ideas on how i could go about this but i am just not sure if it would work. Right now i...
58
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
482
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.