asked on

how to sum up in mysql

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

Ashish Patel

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

spprivate

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

jmokrauer

ASKER

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

dansoto

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.

dansoto

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?

jmokrauer

ASKER

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.

dansoto

So what is missing from the solution by 'asvforce'? His query should do the job..

jmokrauer

ASKER

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

dansoto

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?

jmokrauer

ASKER

No, it will be the value associated with the most recent date earlier than '2001-01-01'.

thanks.

dansoto

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

jmokrauer

ASKER

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.

dansoto

Ok..so then colC is not a sum column.. but rather the unique iD of some record...right?

jmokrauer

ASKER

yes

dansoto

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...

jmokrauer

ASKER

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'

dansoto

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

jmokrauer

ASKER

It looks good but for my application, i need one sql statement. thanks.

ASKER CERTIFIED SOLUTION

dansoto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial