Solved

grouping duplicate* rows in SQL

Posted on 2014-09-05
2
397 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now