March 23, 2006 at 11:47 pm
Good morning Gurus
In need of assistance
I was told that no question is a dumb question so here goes J
Don’t know how to explain my ques but here goes, when I run this query i do not want the result to return duplicate values if I insert distinct it removes all values
Any idea what im missing from the code in order for the result to look like this. It would be greatly appreciated Thanks alot
|
Suplier |
Supp_cnt |
Invoice |
Invoice_cnt |
|
|
1000029 |
1 |
1088 |
5 |
|
|
1000029 |
1 |
1089 |
2 |
|
|
1000029 |
1 |
1092 |
2 |
|
SELECT Supplier, count(Supplier) AS suppl_cnt, SUBSTRING(Invoice, 1, 4) AS Invoice, count(SUBSTRING(Invoice, 1, 4)) AS inv_cnt, COUNT(*)
AS Expr1
FROM ApInvoice
WHERE (InvoiceYear = 2005)
GROUP BY Supplier, Invoice
HAVING 1 <
(Select distinct COUNT(Supplier)
From ApInvoice
Where (InvoiceYear = 2005));
RESULT
|
Suplier |
Supp_cnt |
Invoice |
Invoice_cnt |
Expr_cnt |
|
1000029 |
1 |
1088 |
1 |
1 |
|
1000029 |
1 |
1088 |
1 |
1 |
|
1000029 |
1 |
1088 |
1 |
1 |
|
1000029 |
1 |
1088 |
1 |
1 |
|
1000029 |
1 |
1088 |
1 |
1 |
|
1000029 |
1 |
1089 |
1 |
1 |
|
1000029 |
1 |
1089 |
1 |
1 |
|
1000029 |
1 |
1092 |
1 |
1 |
|
1000029 |
1 |
1092 |
1 |
1 |
March 27, 2006 at 8:00 am
This was removed by the editor as SPAM
March 27, 2006 at 10:57 am
You have GROUP BY Supplier, Invoice, yet you are only displaying a portion of the invoice number. If the rest of the invoice number contains differences, it will generate separate lines, but they will appear to be duplicates. Try putting the SUBSTRING on invoice number into the GROUP BY itself and see what happens.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply