Solved

grouping duplicate* rows in SQL

Posted on 2014-09-05
2
402 Views
Last Modified: 2014-09-09
Can anyone assist with some SQL syntax, I have a base query (see below). It basically links to invoices in our ERP application. We have an apa.amount_paid column, but the issue is it appears 1 or many times based on the number of lines in the invoice. Lines represent various elements of the invoice, and subsequently the amount paid column is appearing numerous times based on the number of lines. So if an invoice had 4 lines, I get 4 rows giving me the same amount paid information i.e. £456.77.
I need a way to crunch the results to only show one row per invoice. Can anyone give any pointers. Each invoice can be grouped either by apa.invoice_id or apa.invoice_num. You will have to excuse my knowledge in this area, very new to it and the SQL DBA is off long time sick so trying to tweak certain queries to get what I need. I fear a group by won’t give me what I need either with the columns currently in the select, and a group by with SUM on the apa.amount_paid won’t work either and would give inaccurate data and total the same apa.amount_paid  amount several times over (i.e if 4 lines 4x the apa.amount_paid).
The troublesome rows are aida.LINE_TYPE_LOOKUP_CODE, aida.distribution_line_number, which is where it breaks the invoice down into several rows. Any pointers?

SELECT
apa.invoice_num,
to_char(apa.amount_paid,'9999999.99') as amount_paid,
apa.invoice_date, aida.accounting_date,
apa.doc_sequence_value as "VOUCHER_NUMBER",
aida.invoice_ID, to_char(aida.amount,'9999999.99') as amount,
aida.LINE_TYPE_LOOKUP_CODE, aida.distribution_line_number,
gcc.code_combination_ID,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
apa.invoice_id,
apa.ACCTS_PAY_CODE_COMBINATION_ID,
apa.vendor_ID,
ass.vendor_ID,
ass.vendor_name

FROM
apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc,
apps.ap_invoices_all apa,
apps.ap_suppliers ass

WHERE
aida.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND aida.invoice_ID = apa.invoice_ID
AND apa.vendor_ID = ass.vendor_ID
AND gcc.segment2 IN (‘123’,’456’,’789’,’000’)
--AND aida.line_type_lookup_code = 'ITEM'
AND apa.amount_paid > 5000
--AND ass. vendor_type_lookup_code = 'ONE'
order by apa.doc_sequence_value
0
Comment
Question by:pma111
2 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 40305886
Can you post some sample data (just the relevant portion no need for every column) that demonstrates your problem and what you would like to see as a result? Please do it in a text format and not as a picture.
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 40306098
What did you expect when you are selecting each "line" as in:

  SELECT Apa.Invoice_Num
       , TO_CHAR ( Apa.Amount_Paid, '9999999.99' ) AS Amount_Paid
       , Apa.Invoice_Date
       , Aida.Accounting_Date
       , Apa.Doc_Sequence_Value AS "VOUCHER_NUMBER"
      , Aida.Invoice_Id
       , TO_CHAR ( Aida.Amount, '9999999.99' ) AS Amount
       , Aida.Line_Type_Lookup_Code
       , Aida.Distribution_Line_Number
      , Gcc.. .
       . . .   E t c  
    FROM Apps.Ap_Invoice_Distributions_All Aida
       , Apps.Gl_Code_Combinations Gcc
       , Apps.Ap_Invoices_All Apa
       , Apps.Ap_Suppliers Ass
   WHERE Aida.Dist_Code_Combination_Id = . . . E t c

If you want one line per invoice, then you need to use an aggregate function like MAX() or SUM() or COUNT() on these columns and group by the other columns, something like this:
  SELECT Apa.Invoice_Num
       , TO_CHAR ( Apa.Amount_Paid, '9999999.99' ) AS Amount_Paid
       , Apa.Invoice_Date
       , MAX ( Aida.Accounting_Date ) AS Aida_Accounting_Date
       , Apa.Doc_Sequence_Value AS "VOUCHER_NUMBER"
       , MAX ( Aida.Invoice_Id ) AS Aida_Invoice_Id
       , TO_CHAR ( SUM ( Aida.Amount ), '9999999.99' ) AS Amount
       , LISTAGG ( Aida.Line_Type_Lookup_Code, ', ' )
            WITHIN GROUP (ORDER BY Aida.Distribution_Line_Number)
            AS Aida_Line_Type_Lookup_Codes
       , COUNT ( Aida.Distribution_Line_Number ) AS Aida_Distribution_Lines
       , Gcc.Code_Combination_Id
       , Gcc.Segment1
       , Gcc.Segment2
       , Gcc.Segment3
       , Gcc.Segment4
       , Apa.Invoice_Id
       , Apa.Accts_Pay_Code_Combination_Id
       , Apa.Vendor_Id
       , Ass.Vendor_Id
       , Ass.Vendor_Name
    FROM Apps.Ap_Invoice_Distributions_All Aida
       , Apps.Gl_Code_Combinations Gcc
       , Apps.Ap_Invoices_All Apa
       , Apps.Ap_Suppliers Ass
   WHERE Aida.Dist_Code_Combination_Id = Gcc.Code_Combination_Id
     AND Aida.Invoice_Id = Apa.Invoice_Id
     AND Apa.Vendor_Id = Ass.Vendor_Id
     AND Gcc.Segment2 IN ('123', '456', '789', '000')
     AND Apa.Amount_Paid > 5000
GROUP BY Apa.Invoice_Num
       , TO_CHAR ( Apa.Amount_Paid, '9999999.99' )
       , Apa.Invoice_Date
       , Apa.Doc_Sequence_Value
       , Gcc.Code_Combination_Id
       , Gcc.Segment1
       , Gcc.Segment2
       , Gcc.Segment3
       , Gcc.Segment4
       , Apa.Invoice_Id
       , Apa.Accts_Pay_Code_Combination_Id
       , Apa.Vendor_Id
       , Ass.Vendor_Id
       , Ass.Vendor_Name
ORDER BY Apa.Doc_Sequence_Value;

Open in new window

:p
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question