December 24, 2003 at 12:16 am
I have 3 tables, the table of Products the table of currency and the table with Customers,arrangements,products and currency
the first 2 tables are in Parent-child format (for OLAP use)
[Products]
Parent-Child-Generation
NULL-PRD-1
PRD-P1-2
PRD-P2-2
P1-P11-3
P1-P12-3
P2-P21-3
P2-P22-3
PRD-P3-2
[Currency]
Parent-Child-Generation
NULL-CURR-1
CURR-EUR-2
CURR-OTH-2
OTH-USD-3
OTH-JPN-3
[Table3]
Customer-Arrang-Curr-Prod
BILL-AR1-EUR-P11
BILL-AR2-EUR-P12
ANNA-AR3-EUR-P21
ANNA-AR4-EUR-P22
BILL-AR5-EUR-P21
BILL-AR6-EUR-P3
BILL-AR7-USD-P11
JOHN-AR8-USD-P11
i want to count all the distinct customers in Table 3 for each generation (1,2,3) for all compinations Product-Currency
for example for the product [P11] and currency [USD] i have 2 distinct customers
for all products [PRD] and currency [OTH] (wich include USD & JPN) i have 2 distinct customers.
so the final result should return the following data:
Product-Currency-Distinct_customers
P11-USD-2
P11-EUR-1
P11-OTH-2
P11-CURR-2
P12-EUR-1
P12-CURR-1
P21-EUR-2
P21-CURR-2
P22-EUR-1
P22-CURR-1
P1-USD-2
P1-EUR-1
P1-OTH-2
P1-CURR-2
P2-EUR-2
P2-CURR-2
P3-EUR-1
P3-CURR-1
PRD-USD-2
PRD-EUR-2
PRD-OTH-2
PRD-CURR-3
any idea?
December 29, 2003 at 10:51 am
Distinct will return distinct rows, so I'd select the combination of all three.
select distinct p.product, c.currency, c.customer
from ...
where ...
as you need them. Then count these rows by embedding the above select as your table.
select product, currency, count(*) as cnt
from (other select here) a
group by product, currency
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 31, 2003 at 6:27 am
quote:
select distinct p.product, c.currency, c.customerfrom ...
where ...
...
You may even get advantage by using "group by p.product, c.currency, c.customer" in stead of "distinct" in this case. Group by is able to use indexes.
Happy NewYear
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply