SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 a question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vital
Starting Member

12 Posts

Posted - 04/20/2008 :  11:09:13  Show Profile  Reply with Quote
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

United Kingdom
1208 Posts

Posted - 04/20/2008 :  11:52:27  Show Profile  Reply with Quote
sorry, this is site is dedicated to Microsoft SQL Server, you probably won't find anyone that is using Firebird.



Em
Go to Top of Page

vital
Starting Member

12 Posts

Posted - 04/20/2008 :  18:48:03  Show Profile  Reply with Quote
yes i use firebird but this subject is about general sql commands:this doesnt interese with firebird i wrote it as information
thanks
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/20/2008 :  19:06:10  Show Profile  Reply with Quote
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.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/20/2008 :  19:12:25  Show Profile  Reply with Quote
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.
Go to Top of Page

vital
Starting Member

12 Posts

Posted - 04/21/2008 :  03:49:31  Show Profile  Reply with Quote
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






Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/21/2008 :  04:39:25  Show Profile  Reply with Quote
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.
Go to Top of Page

vital
Starting Member

12 Posts

Posted - 04/21/2008 :  16:21:05  Show Profile  Reply with Quote
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
Go to Top of Page

vital
Starting Member

12 Posts

Posted - 04/22/2008 :  01:18:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/22/2008 :  01:27:24  Show Profile  Reply with Quote
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
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/22/2008 :  03:48:41  Show Profile  Reply with Quote
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.
Go to Top of Page

vital
Starting Member

12 Posts

Posted - 04/22/2008 :  04:20:27  Show Profile  Reply with Quote
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

Edited by - vital on 04/22/2008 09:02:00
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/22/2008 :  09:40:38  Show Profile  Reply with Quote
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
Go to Top of Page

vital
Starting Member

12 Posts

Posted - 04/23/2008 :  01:04:38  Show Profile  Reply with Quote
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




Edited by - vital on 04/23/2008 02:15:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/23/2008 :  02:31:22  Show Profile  Reply with Quote
1.Post your used query
2.Post your error information
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000