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

 

[aryeh1010]

This could be what you are looking for, if you are looking for all the customers for the branch/item combinations that are received from your original query.

select customer
from
table1
where (branch, item) in
(
select branch, item from
(
SELECT
BRANCH,
ITEM,
COUNT(DISTINCT Customer || '-' || Branch)
FROM
TABLE1
GROUP BY
BRANCH,
ITEM
HAVING
SUM(SALES) BETWEEN 1 AND 1000
)
)

 

[Sridharan]

Thanks aryeh1010 for your response. Infact thats how the Query is written initially and working but when it comes to Performance its a real pain. Infact the table structure i've shown and the condition i'm doing are basically a simple one. There are more conditions to it. So I was basically looking at other alternative ways to get the query done where the performance is not degraded.

I appreciate your reply. Can you come up with some other ideas. Does Window Aggregate Functions can serve my purpose??? I'm working on it.

Thanks
Sri