select join - 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

select join

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 01:08:24

i have 2 table.

table 1 :

UserID Category Value
___________________________
id1 Category1 1
id2 Category1 2
id3 Category3 3
id1 Category2 5
id1 Category1 6

table 2 :

UserID UserName Covering
__________________________________
id1 Name1 id6
id1 Name1 id5
id2 Name2 id7
id3 Name3 id9

Result that i want is to join or map this 2 table to get the result as below.

UserID UserName Category Value
___________________________________
id1 name1 Category1 7(1+6)
id1 name1 Category2 5
id2 name2 Category1 2
id3 name3 Category3 3

That mean that i group by Userid and Category and sum value.
But when i try to use innder join, the value of name1 and category1 is loop 2 time become 14. how to fix this??

Anyone can help, thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 01:13:11

select t1.userid,t2,username,t1.category, sum(t1.value)
from table1 t1
inner join table2 t2 on t1.userid = t2.userid
group by t1.userid,t2,username,t1.category

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 01:20:21

quote:
Originally posted by bklr

select t1.userid,t2,username,t1.category, sum(t1.value)
from table1 t1
inner join table2 t2 on t1.userid = t2.userid
group by t1.userid,t2,username,t1.category

Hi bklr,

Thanks your replay, but it is wrong because at table 2, the name of userid 2 got 2 row. the result will duplicate.

i don't want it duplicate

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 01:32:13

try like this
select t1.userid,t2,username,t1.category, sum(t1.value)
from table1 t1
inner join (select distinct * from table2) t2 on t1.userid = t2.userid
group by t1.userid,t2,username,t1.category

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 01:51:49

[code]select t.UserID,t.UserName,t1.category,t1.Total
from (select distinct UserID, UserName from [table 2])t
inner join (select UserID,Category,SUM(Value) AS Total
from [table 1]
group by UserID,Category)t1
on t1.UserID=t.UserID[/code]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 01:58:11

quote:
Originally posted by bklr

try like this
select t1.userid,t2,username,t1.category, sum(t1.value)
from table1 t1
inner join (select distinct * from table2) t2 on t1.userid = t2.userid
group by t1.userid,t2,username,t1.category

HI bklr,

Ya, we can try to used this.

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 01:59:23

quote:

Originally posted by visakh16

select t.UserID,t.UserName,t1.category,t1.Total
from (select distinct UserID, UserName from [table 2])t
inner join (select UserID,Category,SUM(Value) AS Total
            from [table 1]
            group by UserID,Category)t1
on t1.UserID=t.UserID

HI visakh16,

I may try your sql later on.
Thank you too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 02:04:15

welcome

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 02:21:02

quote:
Originally posted by visakh16

welcome

HI visakh16,

continue from this query,

if at the table 1 have 2 more Datetime column as below.

Userid category value dateFrom dateTo
_______________________________________
id1 category1 5 5/20/2009 5/22/2009

how do i time 5 with (5/22/2009-5/20/2009=3) then get value=15?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 02:25:11

USE SELECT DATEDIFF(DD,DATEFROM, DATETO) * VALUE IN UR SELECT LIST

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 02:31:27

quote:
Originally posted by bklr

USE SELECT DATEDIFF(DD,DATEFROM, DATETO) * VALUE IN UR SELECT LIST

Invalid query,
because the DateFrom and DateTo not in the group by.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 04:00:34

can u post the query ur using

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 04:05:08

quote:
Originally posted by bklr

can u post the query ur using

Ya,

SELECT CATEGORY, userid, COUNT(*) AS TotalCases, SUM(Values) * DATEDIFF(DD,DATEFROM, DATETO) AS Expr1
FROM table1
GROUP BY CATEGORY, userid

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 04:15:20

try like this


SELECT CATEGORY, userid, COUNT(*) AS TotalCases, SUM(Values) * DATEDIFF(DD,DATEFROM, DATETO) AS Expr1
FROM table1
GROUP BY CATEGORY, userid,DATEDIFF(DD,DATEFROM, DATETO)

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-02-16 : 05:32:15

quote:

Originally posted by bklr

try like this


SELECT CATEGORY, userid, COUNT(*) AS TotalCases, SUM(Values) * DATEDIFF(DD,DATEFROM, DATETO) AS Expr1
FROM table1
GROUP BY CATEGORY, userid,DATEDIFF(DD,DATEFROM, DATETO)

Hi bklr,