• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

how to merge two different groups?

I have a query to calculate sale total for each group group by several sales group.

For example,

I have the following groups

Sales
Education
Reseller
Outsourcing
Marketing



Now I want to merge Education group and Reseller group as Reseller group and sum them, remove the education group.

How can I do that in a query?

I am in MS  sQL 2008 R2 env, and this is for MS SOL 2008 data warehouse report - SSIS
0
wasabi3689
Asked:
wasabi3689
  • 6
  • 2
  • 2
  • +2
2 Solutions
 
SharathData EngineerCommented:
Can you post some sample data and expected result?
0
 
wasabi3689Author Commented:
here is my sales group query

select distinct SalesGroup
from   SalesRepDimension
order by SalesGroup

I have the following output

Commercial
Customer Service
Domestic
Domestic Reseller
Educational
Finance
Marketing
Mass Marketing
Partnerships
Press
Technical Support


I have another long query for sum

select....
from....
Where Salesgp=@Salesgroup
0
 
lwadwellCommented:
How are you calling this SQL ... where does @Salesgroup come from?

If @Salesgroup was set to 'Education' and a new variable @Salesgroup2 was set to 'Reseller' would you get the right answer by changing the WHERE clause to:

select....
from....
Where Salesgp IN ( @Salesgroup , @Salesgroup2 )
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
wasabi3689Author Commented:
Salesgroup is a data set and the calculation SQL is another dataset

calculation SQL calling Salesgroup dataset by @SalesGroup
0
 
wasabi3689Author Commented:
Let me explain more what I need

elect distinct SalesGroup
from   SalesRepDimension
order by SalesGroup

I have the following output

Commercial
Customer Service
Domestic
Domestic Reseller
Educational
Finance
Marketing
Mass Marketing
Partnerships
Press
Technical Support

I want to combine two groups Domestic Reseller and Educational as one Educational group

The calculation SQL will pick up one Educational group as sum not two group separately
0
 
wasabi3689Author Commented:
I come up something


select distinct SalesGroup
from   SalesRepDimension
where SalesGroup not in ('Domestic Reseller', 'Educational')
order by SalesGroup
union
select distinct SalesGroup
from   SalesRepDimension
where SalesGroup in ('Domestic Reseller', 'Educational')
order by SalesGroup

But, I still cannot make

select distinct SalesGroup
from   SalesRepDimension
where SalesGroup in ('Domestic Reseller', 'Educational')
order by SalesGroup

as one Educational group
0
 
ralmadaCommented:
So why not grouping Domestic with Domestic Resellers then?

The problem is that you're not explaining how the grouping takes place. How do we know that "Domestic Reseller" and "Educational" should be grouped as one? Is there any other column in your table that will show that linkage? Can you provide more details on that?
0
 
awking00Commented:
select salesgroup from salesrepdimension
where salesgroup <> 'Domestic Reseller'
union [all]
select replace(salesgroup,'Domestic Reseller','Educational') from salesrepdimension
where salesgroup = 'Domestic Reseller'
0
 
awking00Commented:
Rather than continuing to explain what you need, can you post some sample data and the expected results? I suspect that there are more attributes that come into play other than the sales group (e.g. salesperson, sales_amount, sales_date, etc.) and it's not clear as to how you want information returned. If it's just sum(sales_amount) by group, it would be something like the attached example.
group-sales.txt
0
 
wasabi3689Author Commented:
awking00,

The salesgroup did merge with "Educational" only. But,

your suggestion doesn't work because the calculation query will see @SaleGroup. If there is only "Educational" group, it will still only output "Educational", not combine "Domestic reseller"

I want the domestic reseller data added into Educational output. The replace function only replace the domestic name into Educational, but not merge the data
0
 
wasabi3689Author Commented:
I am attaching the query
query.txt
0
 
ralmadaCommented:
maybe something like this?

select rsrd.SalesPersonFullName
      ,revd.CalendarDate
      ,cusd.CustomerName
      ,pd.ProductName
      ,rsf.Quantity
      ,rsf.RecognizedSalesUSD

from   RecognizedSalesFact rsf
       Left Outer Join DateDimension revd on rsf.RevenueDateKey = revd.DateKey
       Left Outer Join SalesRepDimension rsrd on rsf.RevenueSalesRepKey = rsrd.SalesRepKey
       Left Outer Join AddressToSalesRep ats on rsf.AccountAddressKey = ats.AddressKey
       Left Outer Join SalesRepDimension tsrd on ats.SalesRepKey = tsrd.SalesRepKey
       Left Outer Join SalesAttributeDimension sad on rsf.SalesAttributeKey = sad.SalesAttributeKey
       Left Outer Join ProductDimension pd on rsf.ProductKey = pd.ProductKey
       Left Outer Join CustomerDimension cusd on rsf.CustomerKey = cusd.CustomerKey
       
where  revd.CalendarDate = CAST( CONVERT( CHAR(8), @pReportDate, 112) AS DATETIME)
and    case
         when revd.CalendarYear = datename(year, @pReportDate) then
           rsrd.SalesGroup
         else
           tsrd.SalesGroup
        end in (@pSalesGroup, case when @pSalesGroup = 'Domestic Reseller' then 'Educational' else @pSalesGroup end)
--and    (sad.SalesChannelCode = 'Direct' or
--       cusd.CustomerNumber = 28851)
and    rsf.RecognizedSalesUSD <> 0
and    pd.ProductType not in ('Non-Revenue','UNKNOWN')
and    cusd.CustomerNumber <> 216760

Open in new window

0
 
SharathData EngineerCommented:
I don't think I have assisted you here. I should not get any points as I haven't given any suggestion. You can reassign the points appropriately.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now