3 tables query for sum and count

[logidude90]

Hi everyone,

I've been trying to write this query for a little while now and just can't get it to work. The simplified table schema looks something like this:

CUSTOMER
--------------
customerID
customerName
customerType

INVOICES
--------------
customerID
AmmountBilled

CUSTOMER_TYPE
--------------
customerType
TypeDescription

What I'm trying to do is get the total number of customer AND the total ammount from invoice grouped by TypeDescription.

Something like:

Customer type           # of cust.      Ammount($)
Regular customer           x              y
Foreign                    x              y
...                        ...            ...

I can get the ammount total working correctly but not the count(customer)...it always return a number way too large...

Any idea on the type of query I should use?

Thanks in advance!

 

[PHV]

What is your actual SQL code ?
You want a workaround for the lack of COUNT(DISTINCT ...) in JetSQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[logidude90]

Yeah select count(DISTINCT) seems like something that would fix my problem.

SELECT CUSTOMER_TYPE.TypeDescription, Count(CUSTOMER.customerID) AS [NbCustomer], Sum(INVOICES.AmmountBilled) AS [Total ammount for type]
FROM CUSTOMER_TYPE INNER JOIN (CUSTOMER INNER JOIN INVOICES ON CUSTOMER.customerID= INVOICES.customerID) ON CUSTOMER_TYPE.customerType = CUSTOMER.customerTYPE
GROUP BY CUSTOMER_TYPE.customerType;

 

[PHV]

If you want to get the total number of customers having at least one invoice, you may try this (typed, untested):

SELECT T.TypeDescription, G.NbCustomer, Sum(I.AmmountBilled) AS [Total ammount for type]
FROM ((INVOICES I
INNER JOIN CUSTOMER C ON I.customerID = C.customerID)
INNER JOIN CUSTOMER_TYPE T ON C.customerType = T.customerTYPE)
INNER JOIN (SELECT customerTYPE, Count(*) AS NbCustomer FROM (
SELECT DISTINCT X.customerTYPE,X.customerID
FROM CUSTOMER X INNER JOIN INVOICES Y ON X.customerID=Y.customerID
) D GROUP BY customerTYPE
) G ON T.customerType = G.customerTYPE
GROUP BY T.TypeDescription, G.NbCustomer;

If you don't care the invoices for counting the customers:

SELECT T.TypeDescription, G.NbCustomer, Sum(I.AmmountBilled) AS [Total ammount for type]
FROM ((INVOICES I
INNER JOIN CUSTOMER C ON I.customerID = C.customerID)
INNER JOIN CUSTOMER_TYPE T ON C.customerType = T.customerTYPE)
INNER JOIN (SELECT customerTYPE, Count(*) AS NbCustomer FROM (
SELECT DISTINCT customerTYPE,customerID FROM CUSTOMER
) D GROUP BY customerTYPE
) G ON T.customerType = G.customerTYPE
GROUP BY T.TypeDescription, G.NbCustomer;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[logidude90]

Thanks PHV,

It's really appreciated sorry I didn't reply sooner but I was away for the last couple of days.

It seems like it would do what I want it to do, will give it a shot.