syntax 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

SQL Server 2008 Forums

Transact-SQL (2008)

syntax question

Author

Topic

SJones71
Starting Member

6 Posts

Posted - 2011-03-23 : 14:58:30

1st off, I am a SQL novice. I'm using SQL express 2008R2
I have 2 tables....

[table#1]
Item# Type Amount
1122 fee .20
3556 fee .60
1122 tax .05
3556 tax .10

--------
[table#2]

Item# Total
1122
3556

I need a sql script that will read each Item# from table#1 and in the case where the item# appears in multiple rows I need the amounts added and then inserted into table#2.

For example-
Item# 1122 - .20 + .05 = .25 - .25 is then inserted into table#2 in the total column for the row that contains Item#1122

Notes:
The number of rows in table#1 is dynamic
If an item# is present in table#1 it will always be present in table#2
The Amount and Total fields are currency amounts

Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 15:06:50

There's nothing wrong with your syntax -- it doesn't even exist! post what you have some up with so far and we'll help you from there.

Jim

Everyday I learn something that somebody else already knew

SJones71
Starting Member

6 Posts

Posted - 2011-03-23 : 15:35:54

Sorry, I have no syntax. I'm really not sure where to begin.
I really need a few hints to start off with. What I'd really like to know is how do I craft the beginning of the script... to read the item#s in table#1 and where item#s equal then add the amounts. After I get this I think I can figure out the rest.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 15:44:07

Use an inner join

Jim

Everyday I learn something that somebody else already knew

SJones71
Starting Member

6 Posts

Posted - 2011-03-23 : 16:55:25

Thank You Jim. That was the hint I needed... I've already started writing the script... I'll post syntax tomorrow after I get further along.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 18:39:52

Good deal, let us know how it goes or if you have nay questions.

Jim

Everyday I learn something that somebody else already knew

SJones71
Starting Member

6 Posts

Posted - 2011-03-24 : 10:01:55

This is what I have so far...

SELECT inv.Item, inv.Amount
FROM [Sales].[dbo].[Invoice2] AS inv, [Sales].[dbo].[Inventory_Items] AS ebay
INNER JOIN [Sales].[dbo].[Invoice2]
ON Invoice2.Item=Sales.Item_Number
ORDER BY Invoice2.Item

In the Invoice2 table there are 17 rows. In the Inventory_Items table only 14 of the 17 rows the in Invoice2 table are present.
When I run the above script I'm getting 238 rows returned (17x14).
I had expected to only get 14 rows returned.
What am I missing?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-24 : 10:05:00

Probably there is another field to join on. Also, check out this article
http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

Jim

Everyday I learn something that somebody else already knew

SJones71
Starting Member

6 Posts

Posted - 2011-03-24 : 10:06:22

Sorry let me correct myself....

SELECT inv.Item, inv.Amount
FROM [eBay].[dbo].[Invoice2] AS inv, [eBay].[dbo].[ebay_Sold_Items] AS ebay
INNER JOIN [eBay].[dbo].[Invoice2]
ON Invoice2.Item=ebay.Item_Number
ORDER BY Invoice2.Item

In the Invoice2 table there are 17 rows. In the ebay_Sold_Items table only 14 of the 17 rows the in Invoice2 table are present.
When I run the above script I'm getting 238 rows returned (17x14).
I had expected to only get 14 rows returned.
What am I missing?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-24 : 10:29:21

Sorry, I missed this before. You have the table INVOICE2 twice in your from clause, so it's cross joining to that table. I think that's why it's better to use Join statements instead of FROM table1,table2 WHERE

SELECT inv.Item, inv.Amount
FROM [eBay].[dbo].[Invoice2] AS inv
INNER JOIN[eBay].[dbo].[ebay_Sold_Items] AS ebay

ON inv.Item=ebay.Item_Number
ORDER BY inv.Item

Jim

Everyday I learn something that somebody else already knew

SJones71
Starting Member

6 Posts

Posted - 2011-03-24 : 11:38:14

Steve- I looked at the article and that was a big help.

Here's my new syntax...

SELECT
invoice2.Item, count(*) as inv, sum(invoice2.Amount) as total,
count(distinct invoice2.Item) as ItemCount
from
[eBay].[dbo].[Invoice2]
inner join
[eBay].[dbo].[ebay_Sold_Items] ON Invoice2.Item=ebay_Sold_Items.Item_Number
group by
invoice2.Item

Output is now 14 records... like I expect.
Any cleanup suggestions? I still some work to do. I'm going to do some more testing and I'll post again if I need more help.
Thank you very much for you help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources