How can I show total sales results for each customer and product by totaling each sales persons sales per Customer/product

Question

Post reply

How can I show total sales results for each customer and product by totaling each sales persons sales per Customer/product

Steve Kinnaman

SSC Eights!

Points: 839
More actions
July 3, 2009 at 9:34 am

#131768

How can I show total sales results for each customer and product by totaling each sales persons sales per Customer/product
my table looks like this, all columns are nvarcar(50)
SELECT [XCUSNR] --customer number
,[SDGAC] --product number
,[XSALRESP] --Sales person
,[SDSVAL01] --total sum per product and sales person
FROM [Atlas].[crm5].[supoff_gac]
This query (which creates a view) worked fine until I realized that there can be 2 results per customer and procuct number
the product 61 has two results for some customers since there are two sales persons working those companys
selectc.contact_id
,isnull((select sdsval01 from crm5.supoff_GAC_LY where sdgac='39' and xcusnr = c.number2),0) as [39 - AFT, MT && BLM]
--,isnull((select sdsval01 from crm5.supoff_GAC_LY where sdgac='61' and xcusnr = c.number2),0) as [61 - TOOLS - PRO–SERIES]
from
crm5.contact c
The results should be like this but only one line per customer number:
[contact_id][39 - AFT, MT && BLM][60 - ASSEMBLY TOOLS][61 - TOOLS - PRO–SERIES]
496014400
661017020
If i un-comment the line for 61 I recieve this error: "Subquery returned more than 1 value"
results from the table look like this:
XCUSNR SDGAC XSALRESP SDSVAL01
200043NSI61401NSI7578
200075NSI61302NSI4750
244090NSI61103NSI7868
244090NSI61104NSI1918
268644NSI61401NSI4977
268644NSI61402NSI266349
Create the table:
CREATE TABLE [crm5].[supoff_gac_LY](
[XCUSNR] [nvarchar](50) NULL,
[SDGAC] [nvarchar](50) NULL,
[XSALRESP] [nvarchar](50) NULL,
[SDSVAL01] [nvarchar](50) NULL
) ON [PRIMARY]
My goal: is to show the total sales per company and product by totaling up the sales per product by each sales person.
the results will then only show one total per product and company.
there are 21 procucts

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

Please post table structure, sample data and expected output from the sample data so that someone can help you better.

This link will guide you in doing that.

http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D

Pradeep Singh

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Is this what you are looking for:

SELECT XCUSNR,SDGAC,SUM(CAST(SDSVAL01 AS INT)) FROM supoff_gac_LY

GROUP BY XCUSNR,SDGAC

Which returns:

XCUSNR SDGAC Total SDSVAL01

200043NSI 61 7578

200075NSI61 4750

244090NSI619786

268644NSI61267326

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 3

Yes, thats exactly what I needed. You make it look so simple

Thank you very much, this was extremly helpful

steve kinnaman