Grouping Data Problem

Hot Topic

Broker:Corrupted message
Case & Nested table selection and errors
Sample Actions?
Cannot use the special principal 'sa'
Cannnot connect to localhost
Excel Taking to much time to open(10 min)
Delete's Value
SQL Server 2000 installation problems on Windows 2003 Ent x64 cluster
SSIS 'Execute Process task' doen't open window
Reg:Grid and SQL Pane

SQL Server

Newbie: location of Reporting Services and IIS
Tape not available as Backup Device
Can this be Done??
Error: 18456, Severity: 14, State: 11.
Expand All/Collapse All Toggle
File system error: The following error occurred during a file operation: . Parser: An error occurred during pipeline processing.
SQL Accounts in Mirror
Multiple inserts into one table using a stored procedure
attribute key was not found error
OpenRowset to SSAS

Grouping Data Problem

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit ￡100 - credit ￡100 - debit of ￡125 ( I only want to see the debit of ￡125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL

All help appreciated.




Answer this question

Grouping Data Problem

  • gmaenrile

    I am still a little new to SQL so am not entirely sure what the 'schema' is so here's the code from the create table command: -

    Please note that I am importing data from an old DB3 file so don't have a lot of control over the data structure.

    (
    [KEY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [POL_IDX] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL ,
    [ITEM] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
    [AGCY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [BRCH] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [DEPT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [MONTH] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
    [AMT] [float] NULL ,
    [TBAL] [float] NULL ,
    [DESC] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
    [EDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
    [INPUTDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
    [FLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [TRANS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [BILL] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [CPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [STATE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [BCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [ICO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [MCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [COM_P] [float] NULL ,
    [PR] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [PR_P] [float] NULL ,
    [PR2] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [PR_P2] [float] NULL ,
    [BR_P] [float] NULL ,
    [TYPE] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
    [POL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
    [OINT] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
    [LOANNUM] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
    [PRT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [CLOSED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [OP_ID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
    [CSR] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
    [JOURNAL] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
    [DOCTYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [REFER] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
    [PRINTED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [CODE] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
    [PC] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [BIN] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [SPLIT_PLAN] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
    [PR3] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [PR_P3] [float] NULL ,
    [CO_AMT] [float] NULL ,
    [PR_AMT1] [float] NULL ,
    [PR_AMT2] [float] NULL ,
    [PR_AMT3] [float] NULL ,
    [BR_AMT] [float] NULL ,
    [PPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [PFLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [CO_TYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [PR_TYPE1] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [PR_TYPE2] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [PR_TYPE3] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [CPAID_MO] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
    [ADJUST] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [STATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
    [TYPEGROUP] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
    [REFER1] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
    [NOTE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [CONGLOM] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
    [SKIP] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
    [EXTRA] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
    )



  • GeoB

    I have now managed to achived this by using the same approach as I did with Access, however used the min,max options

  • Steven.Dahlin

    hi how does your table look like

    can you please post the schema



  • Grouping Data Problem
Answer this question