Agreegation in CrossTab

Hi Experts,

  Here am using the Web intelligence. I have cross tab  which is displaying State,Product line and Revenue. where as I have multiple product lines which are displaying. out of which I would like to group few objects and sum it .

 For example: I have Accessories,City Skirts,City Trousers,leather,overcoats in this I would like to display the City Skirts+City Trousers together in one column.

Thank you so much for your time.

Regards,
MR
Maruthi Rao YarapathineniAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry for taking so long to answer.
I don't have your original query but based on the Excel you just need to do something like:
SELECT Accessories,	
(CitySkirts, CitySkirts + CityTrousers) AS CitySkirtsTrousers, 
Leather, 
(CitySkirts + OverCoats) AS CitySkirtsOverCoats
FROM TableName

Open in new window

0
 
Maruthi Rao YarapathineniAuthor Commented:
0
 
Maruthi Rao YarapathineniAuthor Commented:
find the attached report file.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
hnasrCommented:
Here is an idea, expressed in Access SQL.
Assume table: a (adesc, type, value)
  Sample data:
adesc	type	value
California	Accessories	15
California	City Skirts	15
California	City Trousers	20
DC	Accessories	15
DC	City Skirts	10
DC	City Trousers	10
Florida	Accessories	20

Open in new window


Create 4 queries:
aggregate_2_columns_1 - from table a excluding the two unrequired values
SELECT a.adesc, a.type, a.value
FROM a
WHERE (((a.type) Not In ("City Skirts","City Trousers")));

adesc	type 	value
Florida	Accessories	20
California	Accessories	15
DC	Accessories	15

Open in new window

aggregate_2_columns_2 - from table a containing the sum of aggregating the 2 columns
SELECT a.adesc, "City Skirts + City Trousers" AS type, Sum(a.value) AS SumOfvalue
FROM a
WHERE (((a.type)="City Skirts" Or (a.type)="City Trousers"))
GROUP BY a.adesc, "City Skirts + City Trousers";

adesc	type	   SumOfvalue
California	City Skirts + City Trousers	35
DC	City Skirts + City Trousers	20

Open in new window

aggregate_2_columns_3 - UNION both queries aggregate_2_columns_1  and
aggregate_2_columns_2
SELECT aggregate_2_columns_1.adesc, aggregate_2_columns_1.type, aggregate_2_columns_1.value
FROM aggregate_2_columns_1
UNION ALL SELECT  aggregate_2_columns_2.adesc, aggregate_2_columns_2.type, aggregate_2_columns_2.SumOfvalue
FROM aggregate_2_columns_2;

adesc	type 	value
Florida	Accessories	20
California	Accessories	15
DC	Accessories	15
California	City Skirts + City Trousers	35
DC	City Skirts + City Trousers	20

Open in new window

aggregate_2_columns_crosstab - from aggregate_2_columns_3
TRANSFORM Sum(aggregate_2_columns_3.value) AS SumOfvalue
SELECT aggregate_2_columns_3.adesc
FROM aggregate_2_columns_3
GROUP BY aggregate_2_columns_3.adesc
PIVOT aggregate_2_columns_3.type;

adesc	Accessories	City Skirts + City Trousers
California	15	35
DC	15	20
Florida	20	

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It would help if you tell us with database are you using (MSSQL, MySQL, Oracle, DB2, Sybase, Access, Excel files, ...?) and also which application is at the frontend.
0
 
Maruthi Rao YarapathineniAuthor Commented:
Sure. Back end is the MSSQL and Front end is SAP Business Objects Web intelligence.

Actually I would like to see


 A B C D are the columns, I would like to see the display as A,B+C,C,B+D . More details are attached as a file with figures.
Requirement.xlsx
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.

All Courses

From novice to tech pro — start learning today.