VenuGopal Papasani wrote:
> Once again i send the table data:
>
> Code Period Value
> c1 2004 22
> c1 2005 10
> c2 2005 15
> c3 2005 20
> c4 2005 15
> c5 2005 5
> c6 2005 30
> c7 2005 25
> c1 2006 5
> c2 2006 15
> c3 2006 40
> c4 2006 30
>
> From this I need the sum of values for period 2005 and codes c1+c2-c4-c5
> (this is not constant its just an example there is lot of codes like
> this..)
>
> For ex:- the result that I want to get is:
> the value for code c1, period 2005 is 10
> for code c2, period 2005 is 15
>
> sum of c1 and c2 is 10 + 15 = 25
>
> The value for code c4, period 2005 is 15
> for code c5, period 2005 is 5
>
> Sum of c4 and c5 is 15 + 5 = 20
>
> Finally the result is (c1+c2) - (c4-c5) = 25 - 20 = 5
You mean, (c1+c2) - (c4+c5), right?
Peter Lauri wrote:
> SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4',
> code, IF(code='c5', code, 0))) FROM datavalue;
You can simplify this using IN. Also, we need to sum the values, not the codes.
SELECT SUM(IF(code IN ('c1', 'c2'), value, 0))
- SUM(IF(code IN ('c4', 'c5'), value, 0)) AS total
FROM datavalue
WHERE period = 2005;
VenuGopal Papasani wrote:
> if it is static then it works fine.but we have lots of codes in a table
> which should be done similar operation.instead varifying staticly with
> c1,c2
> can we make dynamic.
Well,
SELECT SUM(IF(code IN (list of + codes), value, 0))
- SUM(IF(code IN (list of - codes), value, 0)) AS total
FROM datavalue
WHERE period = 2005;
seems simple enough, especially if this is to be truly dynamic.
Another method would be to store a coefficient for each code in a (perhaps
temporary) table.
CREATE TABLE code_values (code CHAR(2), coeff INT);
Set coeff to 1 for codes which specify addition, and -1 for codes that specify
subtraction. For example, to get c1 + c2 - c4 - c5:
INSERT INTO code_values VALUES ('c1', 1), ('c2', 1), ('c4', -1), ('c5', -1);
then join the tables to get your result:
SELECT period, SUM(d.value * cv.coeff) AS total
FROM datavalue d
JOIN code_values cv ON (d.code = cv.code)
WHERE d.period = 2005
GROUP by d.period;
+--------+-------------------------+
| period | SUM(d.value * cv.coeff) |
+--------+-------------------------+
| 2005 | 5 |
+--------+-------------------------+
I would guess that you have a number of standard queries you must run from time
to time. In that case, you could store the coefficients of each query in a
permanent table. For example, if "c1 + c2 - c4 - c5" and "c6 + c7 - c3" were
two standard sums, you could do something like (results are using your sample
data above):
CREATE TABLE sum_queries (query_id INT, code CHAR(2), coeff INT);
INSERT INTO sum_queries VALUES
(1, 'c1', 1), (1, 'c2', 1), (1, 'c4', -1), (1, 'c5', -1),
(2, 'c6', 1), (2, 'c7', 1), (2, 'c3', -1);
SELECT period, SUM(d.value * sq.coeff) AS total
FROM datavalue d
JOIN sum_queries sq ON (d.code = sq.code)
WHERE d.period = 2005
AND sq.query_id = 1
GROUP by d.period;
+--------+-------+
| period | total |
+--------+-------+
| 2005 | 5 |
+--------+-------+
SELECT period, SUM(d.value * sq.coeff) AS total
FROM datavalue d
JOIN sum_queries sq ON (d.code = sq.code)
WHERE d.period = 2005
AND sq.query_id = 2
GROUP by d.period;
+--------+-------+
| period | total |
+--------+-------+
| 2005 | 35 |
+--------+-------+
Of course, a query description table linked by query_id would be a good idea in
this case.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
