Query returns wrong results

G

Guest

Dear friends,

Pls help me with the following problem:

I have 3 tables "clients", "invoices" and "detail_invoice" with one to many
relationships between them.

Clients
ClientID PK
name
address
....

Invoices
InvoiceID PK
ClientID FK
order_date
payment
......

Detail_invoice
DetailID PK
InvoiceID FK
product
quantity
.....

Until here nothing wrong.
But when I try to get the total quantity per order per client and to see in
the same time how many orders we have received per month from the same
client, the number of orders per client (NoOrders) counts in fact the number
of entries in the detail_invoice.

So, to be more exact, the query counts the entries with the same invoiceID
in the detail_invoice (which has more than one entry for each invoice)
instead of counting in invoices table and return the correct answer.

Below is the sql text.


SELECT Client.Name, Count(Client.name) AS NoOrders, Sum(Quantity) AS TotQty,
Month([order_date]) AS Month
FROM Client INNER JOIN Invoices ON Client.ClientId = Invoices.clientId INNER
JOIN detail_invoice ON invoices.InvoiceID = detail_invoice.InvoiceID GROUP
BY Client.name, Month([order_date]);


Can someone help?

Thanks,
Catalin
 
A

Allen Browne

Yes, the query will return the number or rows; that is it returns the number
of invoice detail rows, not the number of invoices.

To avoid that:
1. Create a query using just the Clients and Invoices tables.
Group by the ClientID.
Count the invoices.
Save this query.

2. Create another query, using your first query and the invoice detail table
as invoice tables. You can sum the quantity, and you already have the count
of invoices.

If you want to do it all in one query, you could use a subquery to get the
count of invoices. For help on starting out with subqueries, see:
http://allenbrowne.com/subquery-01.html
 
G

Guest

Thanks a lot. It was really helpful.

Regards,
Catalin

Allen Browne said:
Yes, the query will return the number or rows; that is it returns the number
of invoice detail rows, not the number of invoices.

To avoid that:
1. Create a query using just the Clients and Invoices tables.
Group by the ClientID.
Count the invoices.
Save this query.

2. Create another query, using your first query and the invoice detail table
as invoice tables. You can sum the quantity, and you already have the count
of invoices.

If you want to do it all in one query, you could use a subquery to get the
count of invoices. For help on starting out with subqueries, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Catalin said:
Dear friends,

Pls help me with the following problem:

I have 3 tables "clients", "invoices" and "detail_invoice" with one to
many
relationships between them.

Clients
ClientID PK
name
address
...

Invoices
InvoiceID PK
ClientID FK
order_date
payment
.....

Detail_invoice
DetailID PK
InvoiceID FK
product
quantity
....

Until here nothing wrong.
But when I try to get the total quantity per order per client and to see
in
the same time how many orders we have received per month from the same
client, the number of orders per client (NoOrders) counts in fact the
number
of entries in the detail_invoice.

So, to be more exact, the query counts the entries with the same invoiceID
in the detail_invoice (which has more than one entry for each invoice)
instead of counting in invoices table and return the correct answer.

Below is the sql text.


SELECT Client.Name, Count(Client.name) AS NoOrders, Sum(Quantity) AS
TotQty,
Month([order_date]) AS Month
FROM Client INNER JOIN Invoices ON Client.ClientId = Invoices.clientId
INNER
JOIN detail_invoice ON invoices.InvoiceID = detail_invoice.InvoiceID
GROUP
BY Client.name, Month([order_date]);


Can someone help?

Thanks,
Catalin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top