Solved

how to sum up in mysql

Posted on 2007-11-20
19
519 Views
Last Modified: 2008-02-23
I have a table in mysql called 'x' with columns a,b,c.

I want one sql statement that will sum up the following:

1) the entries in col 'a' where the date  <=  '2001-01-01'
2) the entries in col 'b' where the date  <=  '2001-01-01'
3)  the most recent entry in col 'c' where the date  <  '2001-01-01'

thanks!

Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
InnoDB tables
0
Comment
Question by:jmokrauer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20320382
select Sum(Total) As TotalAll From
(select 'a' as col, sum(a) as Total from x where date  <=  '2001-01-01'
union
select 'b' as col, sum(b) as Total from x where date  <=  '2001-01-01'
union
select 'c' as col, sum(c) as Total from x where date  <  '2001-01-01' ) xyz
0
 
LVL 15

Expert Comment

by:spprivate
ID: 20320383
Select t1.val  +t2.val2
from (Select sum(a+b) as val  from x where date  <=  '2001-01-01' ) t1,
(Select sum(c) as val2  from x where date <  '2001-01-01' ) t2

0
 

Author Comment

by:jmokrauer
ID: 20320425
I dont see how the above solutions are doing the following:

3)  the most RECENT entry in col 'c' where the date  <  '2001-01-01'

Pls explain.  thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:dansoto
ID: 20320507
You are trying to pull 3 columns with a completely different amount of rows in each.  I'm not sure you can do that in one query.
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20320526
BTW...I think your use of the word "sum" has thrown people off when you aren't really looking for a sum.  My understanding is that you want output that looks like:

ColA          ColB          ColC
1                1                 1
2                2                      
3                3      
4                  

Is that correct?
0
 

Author Comment

by:jmokrauer
ID: 20320561
dansoto,

No, I want the sum.  So the answer in the case below would be 21.  ColA & BolB will always have the same number of entries.

ColA          ColB          ColC
1                1                 1
2                2                      
3                3      
4                4

Thanks.
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20320584
So what is missing from the solution by 'asvforce'?  His query should do the job..
0
 

Author Comment

by:jmokrauer
ID: 20320623
I am not sure but I don't think that the final part of his statement (which I put below) selects  the most RECENT entry to add to the sum of the other columns.  I think it sums all the entries before 2001-01-01.

"select 'c' as col, sum(c) as Total from x where date  <  '2001-01-01' )"

thanks for your perseverance & responsiveness



0
 
LVL 7

Expert Comment

by:dansoto
ID: 20320714
Ahh.. well probably confused the issue by including a date range in your question (  <  '2001-01-01).

So since you're pulling the "most recent".. the sum of that column will alway be "1"..right?
0
 

Author Comment

by:jmokrauer
ID: 20320933
No, it will be the value associated with the most recent date earlier than '2001-01-01'.

thanks.
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20321054
Ok..I'm confused.  There can only be 1 most recent date ever... regardless of the timeframe, therefore that value will always be 1...
0
 

Author Comment

by:jmokrauer
ID: 20321071
The value in column 'c' associated with the most recent date might be a one, but it could be 425 or 1.8.  I don't know what value it is without looking it up.
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20321095
Ok..so then colC is not a sum column.. but rather the unique iD of some record...right?
0
 

Author Comment

by:jmokrauer
ID: 20321104
yes
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20321131
So we are back to what I stated a while ago... the term "sum" threw many people off.  You are not looking for a sum in column "C".  Only columns "a" and "b" would contains sum counts...
0
 

Author Comment

by:jmokrauer
ID: 20321165
sum(entries in colA where date<= '2001-01-01' )+sum(entries in colB where date<='2001-01-01' )+value of the most recent entry in colC where date< '2001-01-01'
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20321310
Ok.. .try this then...


SET @a = (select sum(col_a) from x where date <=  '2001-01-01');
SET @b = (select sum(col_b) from x where date <=  '2001-01-01');
SET @c = (select col_c from x where date <= '2001-01-01' order by date desc limit 1);
SET @total = @a+@b+@c;
SELECT @total;

Open in new window

0
 

Author Comment

by:jmokrauer
ID: 20321354
It looks good but for my application, i need one sql statement. thanks.
0
 
LVL 7

Accepted Solution

by:
dansoto earned 500 total points
ID: 20321557
Ok.. try this:
Note that the last select statement has "some_other_column".   We need to select some other column to group by (other than colC) in order to have and even number of columns for the UNIONs to work.  Since it's only returning one value of use, it shouldn't matter which column.
select sum(Total) as complete_total from (
select colA as col, sum(colA) as Total from x where date <= '2001-01-01' group by colA
UNION
select colB as col, sum(colB) as Total from x where date <= '2001-01-01' group by colB
UNION
select some_other_column as col, max(colC) as Total from x where date <= '2001-01-01' group by some_other_column
) as tbl;

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP query / monitor data from Telnet to MySQL 8 97
MySQL limit and not so limited 13 41
Query - Duplicate dates with different activities counts 10 45
SQL query 7 18
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question