Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

grouping duplicate* rows in SQL

Posted on 2014-09-05
2
Medium Priority
?
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

715 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