Android Question Mysql question

tufanv · May 30, 2016

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 · May 31, 2016

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

as we know equation in math is

B4X:

  exp(log(x))=x

or

B4X:

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

or

B4X:

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

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

B4X:

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

B4X:

  log(1.0) = 0.0

you can try this :

B4X:

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 · May 31, 2016

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

B4X:

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

tufanv · May 31, 2016

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

as we know equation in math is

B4X:

exp(log(x))=x

or

B4X:

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

or

B4X:

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

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

B4X:

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

B4X:

log(1.0) = 0.0

you can try this :

B4X:

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 · May 31, 2016

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

B4X:

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 · May 31, 2016

sure, then you need the grouping

B4X:

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

tufanv · May 31, 2016

sorex said:
sure, then you need the grouping

B4X:

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 · May 31, 2016

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

sorex · May 31, 2016

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

tufanv · May 31, 2016

sorex said:
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 · May 31, 2016

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.

B4X:

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 · May 31, 2016

Sorex, Works Excellent ! TY for your help

Android Question Mysql question

tufanv

Expert

nadhiras

Member

sorex

Expert

tufanv

Expert

tufanv

Expert

sorex

Expert

tufanv

Expert

sorex

Expert

sorex

Expert

tufanv

Expert

sorex

Expert

tufanv

Expert

Similar Threads

Android Question Mysql question

Expert

Member

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency