Solved

grouping duplicate* rows in SQL

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax for query to update table 2 29
update statement in oracle 9 29
oracle differnce between two timestamps 5 31
Access join syntax when converting to T-SQL query 4 33
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

840 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