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? - SELECT id, SUM( total_price) AS Total, SUM( tax_price) AS Tax
-
FROM workorder WHERE id = ..... ??
Thanks!
Cheers,
Paul
5 2337
You should drop the id from the selection. -
-- Should be just this
-
SELECT
-
Sum(total_price) AS total,
-
Sum(tax_price) AS tax
-
FROM workorder
-
WHERE id IN (1,4,5,6,8,10);
-
-
-- Output
-
Total Tax
-
68.00 34.00
-
-
-- Using with a UNION to show all the id's requested
-
-
SELECT '' AS id,
-
Sum(total_price) AS total,
-
Sum(tax_price) AS tax
-
FROM workorder
-
WHERE id IN (1,4,5,6,8,10)
-
UNION
-
SELECT id,
-
total_price,
-
tax_price
-
FROM workorder
-
WHERE id IN (1,4,5,6,8,10)
-
-
-- Output
-
id Total Tax
-
68.00 34.00
-
1 2.00 1.00
-
4 8.00 4.00
-
5 10.00 5.00
-
6 12.00 6.00
-
8 16.00 8.00
-
10 20.00 10.00
-
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
Glad it help you out! :-)
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. - SELECT
-
`id`,
-
SUM(`total_price` ) AS `total` ,
-
SUM(`tax_price` ) AS `tax`
-
FROM `workorder`
-
WHERE
-
`id` IN (1, 4, 5, 6, 8, 10)
-
GROUP BY `id`
-
WITH ROLLUP;
- +------+-------+-------+
-
| id | total | tax |
-
+------+-------+-------+
-
| 1 | 2.00 | 1.00 |
-
| 4 | 8.00 | 4.00 |
-
| 5 | 4.00 | 2.00 |
-
| 6 | 8.00 | 4.00 |
-
| 8 | 16.00 | 8.00 |
-
| 10 | 8.00 | 4.00 |
-
| NULL | 46.00 | 23.00 |
-
+------+-------+-------+
Rollup's the best way...
And it'll allow you to do more advanced querying/reporting; as you can see by Atli's post.
Sign in to post your reply or Sign up for a free account.
Similar topics
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:...
|
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,
|
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...
|
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...
|
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...
|
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...
|
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">
<?...
|
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...
|
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...
|
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.
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
| |