Query from the Aggregated Query with different projection

[Sridharan]

Hello All,

Below is how my data looks like

Branch	Item	Customer	Sales
10	1	C1		300
10	1	C2		400
10	1	C3		200
10	2	C1		500
10	2	C3		300
10	3	C1		1200

With this Query

SELECT
	BRANCH,
	ITEM,
	COUNT(DISTINCT Customer || '-' || Branch)
FROM
	TABLE1
GROUP BY
	BRANCH,
	ITEM
HAVING
	SUM(SALES) BETWEEN 1 AND 1000

The output will be

Branch	Item	CustomerCount
10	1	3
10	2	2

and

Item 3 will not come since the value is 1200.

Now I need the Distinct Customers who were part of that Query where the distinct Customer Count is only 3 and not the Sum (5). Now how do we get that value with the given query with all conditions applied.

Remember the SUM(SALES) should be applied to Branch And Item and from that I need the distinct CustomerCount.


Any help is highly appreciated.

Sri

 

[ddrillich]

I tried:

create table sridharan (
Branch   NUMBER,
Item    NUMBER,
Customer CHAR(2),
Sales    NUMBER
);

insert into sridharan values (10,1,'C1',300);
insert into sridharan values (10,1,'C2',400);
insert into sridharan values (10,1,'C3',200);
insert into sridharan values (10,2,'C1',500);
insert into sridharan values (10,2,'C3',300);
insert into sridharan values (10,3,'C1',1200);



SELECT
BRANCH,
ITEM,
COUNT(DISTINCT Customer || '-' || Branch) c
FROM
sridharan
GROUP BY
BRANCH,
ITEM
HAVING
SUM(SALES) BETWEEN 1 AND 1000 and COUNT(DISTINCT Customer || '-' || Branch) = 3;

which returned:

BRANCH ITEM C

10          1      3

Hope it helps,
Dan

 

[Sridharan]

Dan,

Using that query one needs to hardcode the distinct
customer count and also it is going to evaluate at
the Branch, Item level b'coz of the Group By which I
don't want.

To explain it better see the below example

Branch Item Customers Who Have Bought
10 1 C1,C2,C3
10 2 C1,C4,
10 3 C1

Now at each branch level we will get

10 1 3
10 2 2
10 3 1

The above we get by the GROUPING

and now I need a query to get the total which shouldn't be 6 but 4
since the Distinct Customers part of that group by query are only
C1,C2,C3,C4. It should count the Distinct Customers which Group By gave
but without the group by clause that is overall.

One more example

Branch Item Customers
10 1 C1,C2
10 2 C1
10 3 C1

Now at each branch level we will get

10 1 2
10 2 1
10 3 1

and now the total of those shouldn't be 4 but 2 since the Distinct Customers part of that query are only

C1,C2 which were part of the group by query.

Hope I've made my question clear.

Thanks for your time.

Any ideas if someone can comeup with.

Thanks
Sri