Android Question Mysql question

[tufanv]

Hello,

I searched the internet but i couldnt find any info. I need something like this:
My database has many entries lets say a sample data is :

id value success
1 1.45 1
1 1.80 1
1 1.70 1
2 2.40 2
2 3.20 1
2 2.50 1
3 1.60 1
3 1.30 1
3 1.20 1
4 1.40 2
4 1.90 2
4 1.60 1

first, to group by the id all entries
I can do this simply with
select * from tbllist group by id

Now , what i need is :

1) determine the grouped id whichs all the values for "success" is 1 ( i must get grouped ids : 1 and 3 )
2) I need those grouped id 1 and 3 's (which we found in step1 ) values multipled. ( for example for id 1 i need 1.45 * 1.80*1.70 and for id 3 1.60*1.30*1.20 )

Can someone help me with this

TY

 

[nadhiras]

mysql doesnt have built in for mutiplication agregat so we use math function

as we know equation in math is

  exp(log(x))=x

or

  log(a1*a2)=log(a1)+log(a2)

or

 log(a1*a2*a3*)=log(a1)+log(a2)+log(a3))

so .. we can use that equation on sql code like

SELECT ID,EXP(SUM(LOG(value))) AS value FROM table where succes =1  group by id

but if you field can hold NULL value so we use coalesce to replace NULL = 1
remember

  log(1.0) = 0.0

you can try this :

SELECT ID,EXP(SUM(LOG(COALESCE(value,1)))) AS value FROM table where succes =1  group by id

sorry for my bad english
hope you understand

 

[sorex]

you don't need the grouping in this case. this works fine (on mssql)

SELECT EXP(SUM(LOG (value))) AS total FROM tbllist WHERE success=1 AND id IN (1, 3)

 

[tufanv]

mysql doesnt have built in for mutiplication agregat so we use math function

as we know equation in math is

  exp(log(x))=x

or

  log(a1*a2)=log(a1)+log(a2)

or

 log(a1*a2*a3*)=log(a1)+log(a2)+log(a3))

so .. we can use that equation on sql code like

SELECT ID,EXP(SUM(LOG(value))) AS value FROM table where succes =1  group by id

but if you field can hold NULL value so we use coalesce to replace NULL = 1
remember

  log(1.0) = 0.0

you can try this :

SELECT ID,EXP(SUM(LOG(COALESCE(value,1)))) AS value FROM table where succes =1  group by id

sorry for my bad english
hope you understand

thanks for answer =)
I tried the code it only gives the values where success is 1, First of all we have to determine if each success value is 1 then if it is correct continue with the multiply.

I tried you code and it only gave me the rows where success=1 even if there is a success=2 in the group, and also it did not multiply.

 

[tufanv]

you don't need the grouping in this case. this works fine (on mssql)

SELECT EXP(SUM(LOG (value))) AS total FROM tbllist WHERE success=1 AND id IN (1, 3)

Yes Sorex this works fine , it multiplies the entries with id 1 and 3 and gives the results. BUT it multipplies all the items with success=1 for ids 1 and 3 I want to seperate them as
ID total
1 4.437
3 2.496

Whati now get is it multiplies all the values from id1,3 and gives a single result . Is it possible with this way ?

 

[sorex]

sure, then you need the grouping

SELECT EXP(SUM(LOG (value))) AS total FROM tbllist WHERE (success = 1) AND (id IN (1, 3)) GROUP BY id

 

[tufanv]

sure, then you need the grouping

SELECT EXP(SUM(LOG (value))) AS total FROM tbllist WHERE (success = 1) AND (id IN (1, 3)) GROUP BY id

Yes works perfect Sorex TY ! . I only need to implement now the first step . is it possible with mysql ?
I need to find those 1,3 with a code tahat if all the items in grouped id's success=1 then get it else dont get it (for example if for id1 and 3 all the success=1 then get (1,3) but for example if one of the success=2 for id 1 dont get it , get only 3 where all the success is 1

It is a bit complicated but can it be done ?

 

[sorex]

it should already work like that since I added the check for it (where success=1)

 

[sorex]

or do you mean getting a list of IDs where everything per ID is success=1 and not one or more success=0?

 

[tufanv]

or do you mean getting a list of IDs where everything per ID is success=1 and not one or more success=0?

exactly !

success=1 not one or more success = 0 or 2

 

[sorex]

this 'hack' works fine on MSSQL (2008R2) maybe there's a more elegant way, not sure if you use MSSQL, MYSQL or something else tho so you might need to change the case to IIF or something else.

select tl.ID from (
SELECT id,1 as rcount,case  when success=1 then 1 else 0 end as scount FROM tbllist
) as tl
group by id
having sum(rcount)=sum(scount)

 

[tufanv]

Sorex, Works Excellent ! TY for your help