Combining data from 2 tables - 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

Combining data from 2 tables

Author

Topic

yidrasil
Starting Member

21 Posts

Posted - 2011-07-10 : 17:43:44

I have 2 tables, the first contains Name and ProjectCount, the second Name and ProjectTimetaken.

I'm looking to bind results to a graph so am looking to pull together Name, ProjectCount and ProjectTimeTaken into 3 columns.

Table 1

John Smith 6

Table 2

John Smith 12000

Output

John Smith 6 12000
Simon SHaw 12 24000
etc

Note there are multiple entries for John Smith and Simon Shaw so the figs '6' and '12000' (for example) are sums of multiple table entries.

The task then is to

a) Count John Smith ProjectName entries (result= 6)

b) Sum John Smith ProjectTimeTaken entries (result = 12000)

c) Each value (Name, ProjectName, ProjectTimeTaken in individual columns.

d) Row For each name

Guess I'm looking at a Join but Joins fairly new to me.....

Grateful for thoughts as I'm getting mired..........

Many thanks,

Yid

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-10 : 18:25:50

It would be something like this:


select
  p.Name,
  count(ProjectCount) as ProjectCount,
  sum(ProjectTimeTaken) as ProjectTimeTaken
from
  ProjectTable p
  inner join ProjectTimeTable t ON
     p.Name = t.Name
group by
  p.Name

If that does not quite do it for you, can you post some sample data (a few rows) from each table?

yidrasil
Starting Member

21 Posts

Posted - 2011-07-11 : 11:59:05

Hi Sunitabeck,

Thanks for your suggestion. My bad in that I didn't mention each table is in a different catalog - let's call them catalog A (name & Projectcount) and B (name and projecttimetaken)

Can your query be expanded to cater for that situation?

Kind regards,

mark

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-11 : 12:15:48

Tables in SQL Server can be referred using the four-part naming convention. DatabaseServer.DatabaseName.SchemaName.TableName, see here.

So in your example, if the tables are in dbo schema, you would do something like


...
from
  A.dbo.ProjectTable p
  inner join B.dbo.ProjectTimeTable t ON
     p.Name = t.Name
...

X002548
Not Just a Number

15586 Posts

Posted - 2011-07-11 : 12:57:22

if its in the same server/instance you don't need a 4 part name

Just

dbname.owner.table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

yidrasil
Starting Member

21 Posts

Posted - 2011-07-11 : 13:57:57

Folks,

Thanks to you both - have now sorted the two catalog problem thanks to your steer(s).

However, the results of the query offered by Sunitabeck are not returning expect value ranges (i.e. I would expect values of <100 for each 'Name' but am getting a result of ten's of thousands). This is true even though I have a table introduced today where there is known to be a small set of records.

If I might restate the problem;

I am trying to pull together (count/sum) the number of telephone calls an individual makes and how long within a time frame (1 day) that person spends on the phone. I don't need to work out how to select records for a given day as I already have that part. SO....

Table 1 contains multiple records for each name. So each John Smith record = 1 phone call. Therefore count the total occurrences of the name John Smith and you have the number of phone calls made.

Table 2 contains multiple records for each name. Each record contains a name and duration (in seconds) the call lasted. Therefore to determine how long John Smith spent on the phone it's about retrieving all the occurrences of John Smith and summing the values in the associated PhonecallDuration fields.

I am able to do each of the above tasks using individual queries to give me Number of calls by name and Call duration by name. My challenge is to pull those together into a single table if possible so the end result would look like;

Name Number of Calls Duration

John Smith 10 600 (i.e. 60 secs x 10)
Simon Shaw (etc)

Leaving out the twin database issue which I can now do, I'd really appreciate a steer on how the two actions above might be combined given the Name is a common factor between both tables.

I can run 2 queries if needed but pulling the data together into 1 table allows me to relate 2 sets of values to a common name on X-axis of a graph.

Thank you so much for assistance so far...is it possible to have another stab....

Many thanks,

Mark

X002548
Not Just a Number

15586 Posts

Posted - 2011-07-11 : 14:25:53

can you post the DDL and sample data in DML form?

And what those expected results should be?

You'd get an answer in minutes if you post that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

yidrasil
Starting Member

21 Posts

Posted - 2011-07-11 : 17:46:34

Guys;

Herewith an example DDL. If I can refer you to my post above for the general objective;

CREATE TABLE A
(
Name Text,
)
CREATE TABLE B
(
Name Text,
Duration Integer
)
CREATE Table ResultExample
(
Name Text,
TotalCalls Integer,
TotalDuration Integer

)
INSERT INTO B(Name, Duration)

Values ('John Smith',200),
('Simon Shaw',1500),
('John Smith',300),
('Simon Shaw',1200),
('John Smith',100),
('John Smith',600),
('Simon Shaw',1200)

INSERT INTO A(Name)

Values ('John Smith'),
('Simon Shaw'),
('John Smith'),
('Simon Shaw'),
('John Smith'),
('John Smith'),
('Simon Shaw')

INSERT INTO ResultExample(Name,TotalCalls,TotalDuration)

Values ('John Smith',4,1200),
('Simon Shaw',3,3900)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources