select and summarize... - 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 2005 Forums

Transact-SQL (2005)

select and summarize...

Author

Topic

tbg
Starting Member

5 Posts

Posted - 2009-12-30 : 08:50:57

Hi,

i have 2 tables:
1. Agents
2. Sales Data per agent by year and month

e.g.:
Agents

Agent Name Agent ID
Agent 1 101
Agent 2 102
Agent 3 103
Agent 4 104

Sales

Agent ID Year Month Amount
101 2009 1 20
101 2009 2 22
101 2009 3 55
102 2009 1 30
102 2009 3 45
104 2009 7 55
104 2009 8 60
104 2009 9 90

i need to build a view which will display the sales information per agent per period of time.

e.g. display sales of agent 1 for month 1-3 @ 2009.

can anyone assist?

Thanks,

T

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-30 : 08:53:52

select a.agentid,a.agentname,s.year,sum(s.amount)
from agents a
inner join sales s on s.agentid = a.agentid
where s.month between 1 AND 3 AND s.year = 2009
group by a.agentid,a.agentname,s.year

tbg
Starting Member

5 Posts

Posted - 2009-12-30 : 09:03:22

Thanks a lot for the quick response! works like charm...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-30 : 09:06:57

welcome

tbg
Starting Member

5 Posts

Posted - 2010-01-05 : 03:51:09

another question...
table name - problems
id agent status description field 01 field02 field03 etc…
1 100 open can't access url
2 100 close bla bla
3 103 open bla bla bla
4 100 open bla asdfasdf a
5 104 fixed asd fadsf asdf
6 105 open adsf asdfasd;lk
7 105 close asd f asdfa sd
8 102 close a sdfa dsf ads
9 102 close asdfasdf asdf
10 102 open a sdfasdf adsf

table name sales
agent id month year amount
100 10 2009 90
100 11 2009 92
105 1 2009 100
105 2 2009 50
105 3 2009 55
105 4 2009 99

i need to create a view which will display all the data from the problems table with a summary of the sales information for that agent

Please assist,

T

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-05 : 03:58:17

create view dbo.test
as

select p.*,s.year,s.amount
from problems p
inner join (SELECT agentid,year,sum(amount)as amount from sales group by agentid,year)s on s.agentid = p.agentid
group by a.agentid,a.agentname,s.year

tbg
Starting Member

5 Posts

Posted - 2010-01-05 : 04:28:08

Hi,

i tried the following view :

SELECT p.*, s.YEAR, s.amount
FROM dbo._v_Marketing_Prob p INNER JOIN
(SELECT Agent_ID, YEAR, SUM(res) AS amount
FROM dbo._Orders_All
GROUP BY Agent_ID, YEAR) s ON s.Agent_ID = p.Agent_ID
GROUP BY a.Agent_ID, s.YEAR

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.Agent_ID" could not be bound.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-05 : 04:34:01

try this
no need of group by in last statement
SELECT p.*, s.YEAR, s.amount
FROM dbo._v_Marketing_Prob p INNER JOIN
(SELECT Agent_ID, YEAR, SUM(res) AS amount
FROM dbo._Orders_All
GROUP BY Agent_ID, YEAR) s ON s.Agent_ID = p.Agent_ID

tbg
Starting Member

5 Posts

Posted - 2010-01-05 : 04:40:17

bklr thanks for your great assistance!

T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources