|
|
|
|
| Re: UNION QUERY OPTIMIZE [message #131388 is a reply to message #131375] |
Fri, 05 August 2005 13:39  |
quetzalcoatl
Messages: 5
Registered: August 2005
|
Junior Member |
|
|
OK,
I have one table, in this table i have two kinds of products, the way to difference this two products is by two differents fields,i.e.,
field1 field2 field3 field4 field5
------ ------ ------ ------ ------
data1 data12 3 0 1
data2 data22 0 1 2
data3 data23 2 0 1
data4 data24 0 1 2
The products with field5 = 2 are sold only if you buy a product with field5 = 1
The quantity of products sold with field5 = 1 is storaged in field3 and the quantity of products sold with field5 = 2 is storaged in field4
You can note that the product with field5 = 2 has 0 in field3 and that the product with field5 = 1 has 0 in field4.
So i must calcullate how many products ok kind field5 = 2 were sold for each ok kind field5 = 1 of such a way that the formula is:
(summatory of products with field5 = 1 * 100) / (summatory of products with field5 = 1)
the query i have actually is the next
SELECT field1,field2,field3,(SUM(f4) * 100) / SUM(f5) as result
FROM
(
SELECT field1,field2,field3,SUM(field4) as f4,0 as f5 FROM table WHERE condition = 1
GROUP BY field1,field2,field3
UNION
SELECT field1,field2,field3,0 as f4,SUM(field5) as f5 FROM table WHERE condition = 2
GROUP BY field1,field2,field3
)
I know the structure of the table hava a bad design but by the moment i cannot change it, it contains millions of records
In this part
(SUM(f4) * 100) / SUM(f5)
SUM(f5) must not be zero
ThanX!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
to do that query?
