count and sum from other table with id

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 2005 Forums

Transact-SQL (2005)

count and sum from other table with id

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-13 : 14:15:05

Not sure where to begin with this sproc.

Ok, have a simple select:

SELECT importID, name FROM Import

Now, there's another table, Customer

This table has a column I need and a count.
The column amount to perform a sum.
And a count (not a column, a count of records)

The Customer table has the importID column as well and is referenced.

I am pulling all records from Import but for each record
I need a count from Customer where importID =
And a sum from the amount column where importID =

So, will need something like:

SELECT importID, name, cnt, amountSum FROM Import

I hope this is clear as I tried to break it down simply.

Thanks for any input,

Zath

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 14:20:38

[code]
SELECT i.importID, i.name,c.Cnt,c.amountSum
FROM Import i
INNER JOIN (SELECT ImportID,COUNT(*) AS Cnt,SUM(amount) AS amountSum
FROM Customer
GROUP BY ImportID)c
ON c.ImportID=i.ImportID
[/code]

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-13 : 14:41:56

First, thanks for the quick reply.

But having a bit of trouble with it.
First, it didn't like the ImportID.COUNT(*), so I changed it.

Now, it's giving me an invalid column name.

The column name is correct. This is what is causing it to break:

INNER JOIN (SELECT COUNT(*) AS numCustomers
FROM Customer) cust ON a.ImportID = cust.ImportID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 15:24:57

>>First, it didn't like the ImportID.COUNT(*), so I changed it.

Looks like you didn't use what Visakh posted which was to use a <comma> between ImportID and COUNT(*). YOu have a <period>

Be One with the Optimizer
TG

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-13 : 15:25:34

Ok, you actually had it right and I misread it.

It's working!

Thanks...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 15:30:10

welcome

(that was for you Visakh

)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources