a question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

a question

Author

Topic

vital
Starting Member

12 Posts

Posted - 2008-04-20 : 11:09:13

hi there i dont know which topic under i will ask this question im sorry
delphi 7 and i use firebird as database

IBQuery1.SQL.Add('select giris.urunadi, giris.urunkodu , sum(giris.miktariadeti) - sum(cikis.miktariadeti) from giris,cikis where giris.urunkodu=cikis.urunkodu and giris.tarih between ''' +edit1.text+ ''' and ''' + edit2.text+ ''' and cikis.tarih between ''' +edit1.text+ ''' and ''' + edit2.text+ ''' group by giris.urunadi,giris.urunkodu ');

3-this code calculate as wrong result

giris_table

urunadi urunkodu miktari
a 1 10
b 2 123
a 1 2

cikis_table

urunadi urunkodu miktari
a 1 10
b 2 2

the true as result

a 1 10
b 2 121

but it shows and calculates as like this

a 1 -8
b 2 121

whats wrong in this code?

thanks .

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-20 : 11:52:27

sorry, this is site is dedicated to Microsoft SQL Server, you probably won't find anyone that is using Firebird.

Em

vital
Starting Member

12 Posts

Posted - 2008-04-20 : 18:48:03

yes i use firebird but this subject is about general sql commands:this doesnt interese with firebird i wrote it as information
thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-20 : 19:06:10

By 'true result', I presume you mean that's the result you want. If so, why is the first value 10 and not 2 (i.e. 10+2-10)?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-20 : 19:12:25

If it should be 2, then I understand, and the issue is that you need to do some grouping before you do the summing. Something like this...

select giris.urunadi, giris.urunkodu, sum(giris.miktari) - sum(cikis.miktari)
from (
    select urunadi, urunkodu, sum(miktari) as miktari from giris
    group by urunadi, urunkodu) giris, cikis
where giris.urunkodu = cikis.urunkodu
group by giris.urunadi, giris.urunkodu

Alternatively, you could probably do it this way...

select urunadi, urunkodu, sum(miktari) as total
from (        select urunadi, urunkodu, miktari from giris
    union all select urunadi, urunkodu, -miktari from cikis) a
group by urunadi, urunkodu

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

vital
Starting Member

12 Posts

Posted - 2008-04-21 : 03:49:31

thanks RyanRandall

yes im sorry i wrote wrong the true is what you meant
anyways

im not sure but this code doesnt work because if there is a record on the giris table and if the cikis table doesnt has this records name(or number) then the query wont show it .
my problem is this ;
the query calculates the results true as partly i mean it shows as true only 1 line then it calculates others wrong .
i only want this i have 2 table (giris,cikis)
the user will add on the giris table which product comes and if the user sell the products it will add it on the cikis table then my code will calculate there are how many products.

i use this code but it shows the results as wrong.i dont know where my fault is.

select giris.productcode, sum(giris.piece)-sum(cikis.piece) as total from giris,cikis where giris.productcode=cikis.productcode group by giris.productcode

or we can use the giris.productcode or productname.and i used inner join too but it doesnt give me what i want.

and im going to crazy hehe

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-21 : 04:39:25

You need to give more examples of source data and the results you would want for that source data. Pick values that illustrate the points you've made...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

vital
Starting Member

12 Posts

Posted - 2008-04-21 : 16:21:05

thanks for you interesting RyanRandall
but i dont know why it calculate wrong.if there will to be a line then it calculates as true but if there will to be so many lines(records) it calculates wrong.i tried all options(union,inner join) etc etc i will lose my mind this is so simple but i dont know what it doesnt work as true

at least regards RyanRandall

vital
Starting Member

12 Posts

Posted - 2008-04-22 : 01:18:49

hi RyanRandall
i found whats wrong there

as an example my table is so

giris table
name piece
a 3
b 5
b 4
a 8

cikis table
name pieces
b 3
a 5

the query calculate so for products (for a as exam) 3-5+8-5=1 this calculate is not true this must to be so for as a product (3+8)-(5)=6

i found the problems cause but i didnt find the solution.what i can do about it ?do you think distinc?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 01:27:24

quote:
Originally posted by vital

hi RyanRandall
i found whats wrong there

as an example my table is so

giris table
name piece
a 3
b 5
b 4
a 8

cikis table
name pieces
b 3
a 5

the query calculate so for products (for a as exam) 3-5+8-5=1 this calculate is not true this must to be so for as a product (3+8)-(5)=6

i found the problems cause but i didnt find the solution.what i can do about it ?do you think distinc?

thanks

Can you try like this?
miktari

SELECT t1.urunadi ,t1.urunkodu,t1.miktari- ISNULL(t2.miktari,0) as 
FROM (SELECT urunadi ,urunkodu,sum(miktari) as miktari
      FROM giris
      GROUP BY urunadi ,urunkodu)t1
LEFT JOIN (SELECT urunadi ,urunkodu,sum(miktari) as miktari
      FROM cikis
      GROUP BY urunadi ,urunkodu)t2
ON t1.urunadi =t2.urunadi
AND t1.urunkodu=t2.urunkodu

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 03:48:41

quote:
Originally posted by vital

hi RyanRandall
i found whats wrong there

as an example my table is so

giris table
name piece
a 3
b 5
b 4
a 8

cikis table
name pieces
b 3
a 5

the query calculate so for products (for a as exam) 3-5+8-5=1 this calculate is not true this must to be so for as a product (3+8)-(5)=6

i found the problems cause but i didnt find the solution.what i can do about it ?do you think distinc?

thanks

You still haven't given an example which shows why my original suggestion doesn't work. With your new structure, I get 6 for 'a' - as required...

-- Sample
declare @giris table (name varchar(10), piece int)
insert @giris
          select 'a', 3
union all select 'b', 5
union all select 'b', 4
union all select 'a', 8 

declare @cikis table (name varchar(10), pieces int)
insert @cikis
          select 'b', 3
union all select 'a', 5

-- Calculation
select name, sum(piece) as total
from (        select name, piece from @giris
    union all select name, -pieces from @cikis) a
group by name

/*
name       total
---------- -----------
a          6
b          6
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

vital
Starting Member

12 Posts

Posted - 2008-04-22 : 04:20:27

thank you very much visakh16 and RyanRandall

great!

i solved the problem with your code RyanRandall
there is no a problem for now

name total spent in
---------- ----------- -------- ------
a 6
b 6

i must put there 2 sum too but i dont know where i need to put

this code has error
urunkodu,urunadi,cOALESCE(sum(miktariadeti),0) as Total from(select urunkodu,urunadi,miktariadeti from giris union all select urunkodu,urunadi,-COALESCE(miktariadeti,0),sum(cikis.miktariadeti) as harcanan from cikis) a group by urunkodu,urunadi

best regards

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-22 : 09:40:38

quote:
Originally posted by vital

this code has error
urunkodu,urunadi,cOALESCE(sum(miktariadeti),0) as Total from(select urunkodu,urunadi,miktariadeti from giris union all select urunkodu,urunadi,-COALESCE(miktariadeti,0),sum(cikis.miktariadeti) as harcanan from cikis) a group by urunkodu,urunadi

best regards

you've no SELECT at the start, and your union in the derived table has 3 columns in the first part and 4 columns in the 2nd

if that's just how you ran i'm not surprised it has an error though it wuold help to know exactly what the error was..?

Em

vital
Starting Member

12 Posts

Posted - 2008-04-23 : 01:04:38

no elancaster

i solved the problem this gives me what i want

select urunadi,sum(miktariadeti) as total from(select urunadi,miktariadeti from giris union all select urunadi,-miktariadeti from cikis) a group by urunadi
this gives me like this a result

name total
-------- -----------
a 6
b 6

i wantto get 2 coloumns(spent,in) more like this

i wanna like this a result

name total spent in
---------- ----------- -------- ------
a 6 ? ?
b 6

i added some fields(spent,in) in that code but it has some error
also how i can get these spent and in coloumns too?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 02:31:22

1.Post your used query
2.Post your error information

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources