SQL Query problem - 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

SQL Query problem

Author

Topic

Tks
Starting Member

48 Posts

Posted - 2007-02-07 : 12:42:49

HI i have

in one tables the sales (outs)
(sow x rows per article) and in a another table i have only the ins 1 row per article.

I need to know the amount i have brought in total but only from the articels sold.

The problem is that the ordinairy sum multiplyes by the amount of rows sow every row wich is found in sold gives me again the x amount of ins.

Tried a bit with SUM(distinct but it didn´t work)

anyone?

if i do groupby article then i see the correct list but offcourse ic ant sum that list by query can i?

Tks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-08 : 00:06:38

It will be helpful if you could post table structures, sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Tks
Starting Member

48 Posts

Posted - 2007-02-08 : 02:01:41

OK, Sorry,

Table Sales (OUT):
Article ID
Amount Sold
Price Ex vat
GroupID
Date

Rows in out:
Article 1 , date , amount : 3
Article 1 , date , amount : 1
Article 2 , date , amount : 1

Table (IN)
Article ID
Amount
Date
GroupID

Article 1 , date , amount : 5
Article 2 , date , amount : 5

In table IN every article is only listen once.
In table OUT it depends on the sales you have got.

If i do SUM(IN) inner join out i get as answer:
15 + 5 + 5 = 25

If i do sum(in) group by article id i get the correct answer except that it is broken down in ids
Article 1: 5
Article 2: 5

Sow what i need is just the answer 10.

this way you can easyly see if you sold more then you have brought over the article wich are sold

Tks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 02:51:58

1) Your sample data does not meet the each table's DDL.
2) Your sample data does not meet your critera for the expected output, ie where do the 15 come from?

Peter Larsson
Helsingborg, Sweden

Tks
Starting Member

48 Posts

Posted - 2007-02-08 : 03:45:05

1) Your sample data does not meet the each table's DDL.
2) Your sample data does not meet your critera for the expected output, ie where do the 15 come from?

Peter Larsson
Helsingborg, Sweden

your first thing dunno what DDL is.
2nd 15 = 3 x 5

Need to rectify this "If i do SUM(IN) inner join out i get as answer:
15 + 5 + 5 = 25" to

If i do SUM(IN) inner join out i get as answer:
5 + 5 + 5 = 15

Sow it sums for every row found the IN amount to my total.

and i only need for every distinct item to sum his IN amount to the total.
SUM(distinct didn´t worked)..

clear??

Tks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 04:16:20

No, absolutely not.

Try to restate your problem like this:

-- prepare sample data
declare @mytable1 table (col1 int, col2 money, ...)
insert @mytable1 values (..., ..., ...)
insert @mytable1 values (..., ..., ...)

declare @mytable2 table (col1 int, col2 money, ...)
insert @mytable2 values (..., ..., ...)
insert @mytable2 values (..., ..., ...)

And now you supply your expected output based on the sample data in the code.

Peter Larsson
Helsingborg, Sweden

Tks
Starting Member

48 Posts

Posted - 2007-02-08 : 08:03:18

what a hassle to sketch a situation...

-- prepare sample data
declare @sales table (Id int, Amount nummeric)
insert @sales values (1, 3)
insert @sales values (1, 1)
insert @sales values (2, 1)

declare @brought table (Id int, Amount Nummeric )
insert @brought values (1,5)
insert @brought values (2,3)
insert @brought values (3,4)

Now i need to know the amount brought by inner joining the sales table.

the correct answer is 8. but because there are multiple rows in the sales table
it multiplies the brought amount per article by the every row found on that ID

sow it does in mem
SOLD:
1, 2 x 5
2, 1 x 3

and comes with the answer 13 wich is wrong

Tks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-08 : 08:16:07

Is this what you mean?

declare @sales table (Id int, Amount numeric)
insert @sales values (1, 3)
insert @sales values (1, 1)
insert @sales values (2, 1)

declare @brought table (Id int, Amount Numeric )
insert @brought values (1,5)
insert @brought values (2,3)
insert @brought values (3,4)

-- this is wrong
select sum(b.amount)
from @brought b join @sales s
on b.[id] = s.[id]

-- this is correct
select sum(b.amount)
from @brought b join (select distinct id from @sales) s
on b.[id] = s.[id]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Tks
Starting Member

48 Posts

Posted - 2007-02-08 : 08:28:15

Yupsz

that should it be... 2 in 1 i call it. did now it exist.. didn't know when :D
now i do :D

Tks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 08:59:48

Is it something like this you want?

SELECT		s.ID AS [Sales ID],
		b.Amount AS [Brought Amount]
FROM		(
			SELECT DISTINCT	ID
			FROM		@Sales
		) AS s
INNER JOIN	@Brought AS b ON b.ID = s.ID

The output of the above code is

Sales ID	Brought Amount
1		5
2		3

quote:
Originally posted by Tks

what a hassle to sketch a situation...

I can't decide how fast or how slow or if at all you get an answer or a suggestion to your problem. It is you!
By giving more information to us (you have to remember we do not know anything about your system and your business rules), it gives us the chance to get a better picture what you want.

Giving more information -> Faster and better suggestions
Giving less information -> Slower, non-working or no suggestions at all.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 09:03:49

[code]-- prepare sample data
declare @sales table (Id int, Amount int)
insert @sales values (1, 3)
insert @sales values (1, 1)
insert @sales values (2, 1)

declare @brought table (Id int, Amount int)
insert @brought values (1,5)
insert @brought values (2,3)
insert @brought values (3,4)

-- show the result 1
SELECT SUM(b.Amount) AS [Brought Amount]
FROM @Brought AS b
WHERE b.ID IN (SELECT s.ID FROM @Sales AS s)

-- show the result 2
SELECT SUM(b.Amount) AS [Brought Amount]
FROM @Brought AS b
WHERE EXISTS (SELECT NULL FROM @Sales AS s WHERE s.ID = b.ID)

-- show the result 3
SELECT SUM(b.Amount) AS [Brought Amount]
FROM @Brought AS b
INNER JOIN (
SELECT DISTINCT ID
FROM @Sales
) AS s ON s.ID = b.ID[/code]

Peter Larsson
Helsingborg, Sweden

Tks
Starting Member

48 Posts

Posted - 2007-02-08 : 10:53:38

need the sum of this: The output of the above code is
Sales ID Brought Amount
1 5
2 3

you are right about the info thing.
its just that i this while working and the bos is flipping if he see mee doing this.

anyway need the result 8 like said before will try with your first solution.

Tks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources