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
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