• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

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
0
jmokrauer
Asked:
jmokrauer
1 Solution
 
Ashish PatelCommented:
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
 
spprivateCommented:
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
 
jmokrauerAuthor Commented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
dansotoCommented:
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
 
dansotoCommented:
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
 
jmokrauerAuthor Commented:
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
 
dansotoCommented:
So what is missing from the solution by 'asvforce'?  His query should do the job..
0
 
jmokrauerAuthor Commented:
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
 
dansotoCommented:
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
 
jmokrauerAuthor Commented:
No, it will be the value associated with the most recent date earlier than '2001-01-01'.

thanks.
0
 
dansotoCommented:
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
 
jmokrauerAuthor Commented:
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
 
dansotoCommented:
Ok..so then colC is not a sum column.. but rather the unique iD of some record...right?
0
 
jmokrauerAuthor Commented:
yes
0
 
dansotoCommented:
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
 
jmokrauerAuthor Commented:
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
 
dansotoCommented:
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
 
jmokrauerAuthor Commented:
It looks good but for my application, i need one sql statement. thanks.
0
 
dansotoCommented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now