Hello, This has got me stumped. I'm writing a very simple query that pulls Sales Dollars by Sales Representative. The issue I'm running into is I have a few Sales Reps in the system that need to be treated as one. For example, we have two Sales Reps in the system, John and John Smith. When I run the query I want all of John's sales to show under John Smith's sales as they are the same person outside of the system. Can anyone shed some light on how I can accomplish this?

There are a few methods: 1. Have another table that links John and John Smith into one entity, join with that table and use the field from that table to group by, not the original Sales Rep 2. If the cases of such problem are known, you can use case expression and repeat it in the group by, e.g. select case when [Sales Rep] IN ('John','John Smith') then 'John Smith' else [Sales Rep] end as [Sales Rep], sum(SalesAmount) as [Total Sales] from SalesInfo GROUP BY case when [Sales Rep] IN ('John','John Smith') then 'John Smith' else [Sales Rep] end ------------- The first solution is the recommended solution.For every expert, there is an equal and opposite expert. - Becker's Law My blog

Thank you! I tried option two and it worked perfectly, however, once I added multiple reps it broke. In other words, with the following it worked: case when c.slprsnid IN ('John','John Smith') then 'John Smith' else c.slprsnid end as slprsnid But with this it didn't: case when c.slprsnid IN ('John','John Smith') then 'John Smith' else c.slprsnid end as slprsnid, case when c.slprsnid IN ('Susan','Susan Planter') then 'Susan Planter' else c.slprsnid end as slprsnid It fails with "The column 'slprsnid' was specified multiple times"

You need to use one expression for everything, e.g. case when c.SlPrsnID IN ('John','John Smith') then 'John Smith' when c.SlPrsnID IN ('Susan','Susan Planter') then 'Susan Planter' ELSE c.SlPrsnID END AS SnPrsnID If there are many cases like that creating an extra linking table is a better option. For every expert, there is an equal and opposite expert. - Becker's Law My blog

I changed it to this and it worked! Thanks! case when c.slprsnid IN ('John','John Smith') then 'John Smith' else c.slprsnid when c.slprsnid IN ('Susan','Susan Planter') then 'Susan Planter' else c.slprsnid end as slprsnid

You can not have two else portions in the case - I assume it's a typo and you used the same syntax I showed.For every expert, there is an equal and opposite expert. - Becker's Law My blog