help with query

Hi, need some help...
The problem I face is that the last column in the select list which is
[Amount2]
returns the sum of all users sum so each row in the returned result has the
same number.
The [Amount1] column returns the correct sum per each user due to the
grouping.
How shall I do this?
SELECT u.Col1, u.Col2, u.Col3, SUM(t.amount) AS Amount1 /* this SUM is OK
*/,
(SELECT SUM(t.amount) /* this SUM is not OK */
FROM dbo.Table1 T1
INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
WHERE T3.UserId IN (1, 2, 3)
AND T3.TypeId = 2
) AS Amount2
FROM dbo.Table1 T1
INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
WHERE T3.UserId IN (1, 2, 3)
AND T3.TypeId = 1
GROUP BY T4.Col1, T4.Col2, T4.Col3
Thanks,
YanivYaniv
I did some testing on Northwind database
select Customerid, count(employeeid),
(select count(employeeid) from orders
where Customerid in ('vinet','hanar')) as d
from orders
where Customerid in ('vinet','hanar')
group by Customerid
--Customerid d
-- -- --
HANAR 14 19
VINET 5 19
select Customerid, count(employeeid),
(select count(o.employeeid) from orders o
where orders.Customerid=o.Customerid) as d
from orders
where Customerid in ('vinet','hanar')
group by Customerid
--Customerid d
-- -- --
HANAR 14 14
VINET 5 5
You did not have a group by customerid in my case ,thus you've got the
wrong output
See if my example helps you otherwise please post your ddl + expected result
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:ettjcwM9FHA.2264@.tk2msftngp13.phx.gbl...
> Hi, need some help...
> The problem I face is that the last column in the select list which is
> [Amount2]
> returns the sum of all users sum so each row in the returned result has
> the same number.
> The [Amount1] column returns the correct sum per each user due to the
> grouping.
> How shall I do this?
> SELECT u.Col1, u.Col2, u.Col3, SUM(t.amount) AS Amount1 /* this SUM is OK
> */,
> (SELECT SUM(t.amount) /* this SUM is not OK */
> FROM dbo.Table1 T1
> INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
> INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
> INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
> WHERE T3.UserId IN (1, 2, 3)
> AND T3.TypeId = 2
> ) AS Amount2
> FROM dbo.Table1 T1
> INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
> INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
> INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
> WHERE T3.UserId IN (1, 2, 3)
> AND T3.TypeId = 1
> GROUP BY T4.Col1, T4.Col2, T4.Col3
>
> Thanks,
> Yaniv
>|||Great, this is what I need.
Many many thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23$x6W5M9FHA.132@.TK2MSFTNGP15.phx.gbl...
> Yaniv
> I did some testing on Northwind database
> select Customerid, count(employeeid),
> (select count(employeeid) from orders
> where Customerid in ('vinet','hanar')) as d
> from orders
> where Customerid in ('vinet','hanar')
> group by Customerid
> --Customerid d
> -- -- --
> HANAR 14 19
> VINET 5 19
>
> select Customerid, count(employeeid),
> (select count(o.employeeid) from orders o
> where orders.Customerid=o.Customerid) as d
> from orders
> where Customerid in ('vinet','hanar')
> group by Customerid
> --Customerid d
> -- -- --
> HANAR 14 14
> VINET 5 5
>
> You did not have a group by customerid in my case ,thus you've got the
> wrong output
> See if my example helps you otherwise please post your ddl + expected
> result
>
>
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:ettjcwM9FHA.2264@.tk2msftngp13.phx.gbl...
>