SELECT sum Question
-
*Forgive the formating but I couldn't figure out how to represent rows and columns.
I have a table (sales) with salesmen and totals.
Table:sales
Salesman--------Totals
---509------------100.00
---510------------200.00
---513------------500.00
Let's say that salesman 509 and 510 are working as a team and want the total of both of their sales.
What kind of a query would let me add the Toals of Salesman 509 and 510 and output the result as:
Team 300.00
-
Can't fully answer this one for a lack of time.
But you need to have a team table and a TeamSalesMan Table.
You then join the sale tables to TeamSalesMan to teams and then run the group by on the teamid or teamname.
-
I'm sorry but I'm not following you.
-
You need a table that maps the salesmen to a team (actually 2 tables).
If you don't have that you can do a cased group by but I would strongly advise against that.
-
Something along the line of this:
create
table #Sales (
SalesmanID int,
Totals money)
insert
into #sales values (509, 100)
insert
into #sales values (510, 200)
insert
into #sales values (511, 500)
create
table #SalesPerson (
SalesmanID int,
SalesTeam int)
insert
into #SalesPerson values (509, 1)
insert
into #SalesPerson values (510, 1)
insert
into #SalesPerson values (511, 2)
select
a.SalesTeam, sum(b.Totals)
from
#SalesPerson a
inner
join #Sales B
on a.SalesmanID = b.SalesmanID
group
by a.SalesTeam
-
Absoulty fantastic!
Mine ended up looking like this mess because I'm calculating a bunch of sales numbers first but your solution works flawlessly.
Thank you.
SELECT dbo.corda_main.salesman,
SUM(dbo.corda_FYdollars_pc0topc499.fydollarspc142 + dbo.corda_FYdollars_pc0topc499.fydollarspc135 + dbo.corda_FYdollars_pc0topc499.fydollarspc136 + dbo.corda_FYdollars_pc0topc499.fydollarspc137
+ dbo.corda_FYdollars_pc0topc499.fydollarspc140 + dbo.corda_FYdollars_pc0topc499.fydollarspc141 + dbo.corda_FYdollars_pc0topc499.fydollarspc101
+ dbo.corda_FYdollars_pc0topc499.fydollarspc102 + dbo.corda_FYdollars_pc0topc499.fydollarspc103 + dbo.corda_FYdollars_pc0topc499.fydollarspc104
+ dbo.corda_FYdollars_pc0topc499.fydollarspc105 + dbo.corda_FYdollars_pc0topc499.fydollarspc106 + dbo.corda_FYdollars_pc0topc499.fydollarspc107
+ dbo.corda_FYdollars_pc0topc499.fydollarspc110 + dbo.corda_FYdollars_pc0topc499.fydollarspc111 + dbo.corda_FYdollars_pc0topc499.fydollarspc112
+ dbo.corda_FYdollars_pc0topc499.fydollarspc113 + dbo.corda_FYdollars_pc0topc499.fydollarspc114 + dbo.corda_FYdollars_pc0topc499.fydollarspc115
+ dbo.corda_FYdollars_pc0topc499.fydollarspc116 + dbo.corda_FYdollars_pc0topc499.fydollarspc117 + dbo.corda_FYdollars_pc0topc499.fydollarspc118
+ dbo.corda_FYdollars_pc0topc499.fydollarspc119 + dbo.corda_FYdollars_pc0topc499.fydollarspc120 + dbo.corda_FYdollars_pc0topc499.fydollarspc125
+ dbo.corda_FYdollars_pc0topc499.fydollarspc126 + dbo.corda_FYdollars_pc0topc499.fydollarspc127 + dbo.corda_FYdollars_pc0topc499.fydollarspc129
+ dbo.corda_FYdollars_pc0topc499.fydollarspc130 + dbo.corda_FYdollars_pc0topc499.fydollarspc131 + dbo.corda_FYdollars_pc0topc499.fydollarspc143
+ dbo.corda_FYdollars_pc0topc499.fydollarspc147 + dbo.corda_FYdollars_pc0topc499.fydollarspc148 + dbo.corda_FYdollars_pc0topc499.fydollarspc150
+ dbo.corda_FYdollars_pc0topc499.fydollarspc170 + dbo.corda_FYdollars_pc0topc499.fydollarspc171 + dbo.corda_FYdollars_pc0topc499.fydollarspc172
+ dbo.corda_FYdollars_pc0topc499.fydollarspc175 + dbo.corda_FYdollars_pc0topc499.fydollarspc155 + dbo.corda_FYdollars_pc0topc499.fydollarspc156
+ dbo.corda_FYdollars_pc0topc499.fydollarspc157 + dbo.corda_FYdollars_pc0topc499.fydollarspc162 + dbo.corda_FYdollars_pc0topc499.fydollarspc160
+ dbo.corda_FYdollars_pc0topc499.fydollarspc161 + dbo.corda_FYdollars_pc0topc499.fydollarspc163 + dbo.corda_FYdollars_pc0topc499.fydollarspc164
+ dbo.corda_FYdollars_pc0topc499.fydollarspc185 + dbo.corda_FYdollars_pc0topc499.fydollarspc190 + dbo.corda_FYdollars_pc0topc499.fydollarspc195
+ dbo.corda_FYdollars_pc0topc499.fydollarspc200 + dbo.corda_FYdollars_pc0topc499.fydollarspc201 + dbo.corda_FYdollars_pc0topc499.fydollarspc202
+ dbo.corda_FYdollars_pc0topc499.fydollarspc203 + dbo.corda_FYdollars_pc0topc499.fydollarspc206 + dbo.corda_FYdollars_pc0topc499.fydollarspc204
+ dbo.corda_FYdollars_pc0topc499.fydollarspc207 + dbo.corda_FYdollars_pc0topc499.fydollarspc420 + dbo.corda_FYdollars_pc0topc499.fydollarspc425
+ dbo.corda_FYdollars_pc0topc499.fydollarspc147 + dbo.corda_FYdollars_pc0topc499.fydollarspc215 + dbo.corda_FYdollars_pc0topc499.fydollarspc216
+ dbo.corda_FYdollars_pc0topc499.fydollarspc230 + dbo.corda_FYdollars_pc0topc499.fydollarspc231 + dbo.corda_FYdollars_pc0topc499.fydollarspc232
+ dbo.corda_FYdollars_pc0topc499.fydollarspc235 + dbo.corda_FYdollars_pc0topc499.fydollarspc250 + dbo.corda_FYdollars_pc500topc999.fydollarspc973 + dbo.corda_FYdollars_pc500topc999.fydollarspc974 + dbo.corda_FYdollars_pc500topc999.fydollarspc975
+ dbo.corda_FYdollars_pc500topc999.fydollarspc976 + dbo.corda_FYdollars_pc500topc999.fydollarspc977
+ dbo.corda_FYdollars_pc500topc999.fydollarspc978 + dbo.corda_FYdollars_pc500topc999.fydollarspc979 + dbo.corda_FYdollars_pc0topc499.fydollarspc394 + dbo.corda_FYdollars_pc0topc499.fydollarspc395 + dbo.corda_FYdollars_pc0topc499.fydollarspc396
+ dbo.corda_FYdollars_pc0topc499.fydollarspc397 + dbo.corda_FYdollars_pc500topc999.fydollarspc651 + dbo.corda_FYdollars_pc500topc999.fydollarspc652
+ dbo.corda_FYdollars_pc500topc999.fydollarspc653) AS [cfy]
INTO #totals
FROM dbo.corda_main INNER JOIN
dbo.corda_FYdollars_pc0topc499 ON
dbo.corda_main.codisaccountnumber = dbo.corda_FYdollars_pc0topc499.codisaccountnumber INNER JOIN
dbo.corda_FYdollars_pc500topc999 ON
dbo.corda_main.codisaccountnumber = dbo.corda_FYdollars_pc500topc999.codisaccountnumber INNER JOIN
dbo.corda_LFYdollars_pc0topc499 ON
dbo.corda_main.codisaccountnumber = dbo.corda_LFYdollars_pc0topc499.codisaccountnumber INNER JOIN
dbo.corda_LFYdollars_pc500topc999 ON
dbo.corda_main.codisaccountnumber = dbo.corda_LFYdollars_pc500topc999.codisaccountnumber
GROUP BY dbo.corda_main.salesman
select dbo.corda_teams.team,sum(#totals.cfy) as [team total]
from corda_teams inner join #totals on
corda_teams.salesman = #totals.salesman
group by corda_teams.team
drop table #totals
-
How come this ain't in the format :
SaleManID, SaleAmount??
-
Ummmm.... You might want to read up on what a "Table Alias" is... it'll make that code look a lot cleaner...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey Remi and Anders... nice tag team you guys made!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yup, I had an emergency and I couldn't provide the full answer. That's why we are called a community after all. Thanx Anders.
-
You're welcome.
Those that have seen my code here and in other posts know I believe in making it as simple and general as possible, figure that way people that have similar but not the same problem might find something usefull.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply