Collating SQL query results

Question

I am having to write a query for an AS400 report. We are looking to group data by date. In other words we want to sum all data for each individual year and month. This is the query I have currently:

Select SCDATA.SCCLNT.CCLNT,
  (Select SCDATA.SCCLNT.CNAME From SCDATA.SCCLNT
  Where SCDATA.SCCLNT.CLTGRP Like 916500 Fetch First 1 Rows Only) As ClientName,
  CONCAT(TRIM(SCDATA.SCCLNT.CADD1), SCDATA.SCCLNT.CADD2) As Address1,
  CONCAT(TRIM(SCDATA.SCCLNT.CCITY), CONCAT(', ',
  CONCAT(TRIM(SCDATA.SCCLNT.CSTATE), CONCAT('  ', TRIM(SCDATA.SCCLNT.CZIP)))))
  As Address2,
  SCDATA.SCCLNT.CLTGRP As Group,
  SCDATA.SCPLHS.HMONTH || '-' || SCDATA.SCPLHS.HYEAR AS EndDate,

sum(HPL#) as Placed#,
sum (hpl$) as Placed$,
sum(HPMT$M) as PymtMth,
sum(HPMT$) as PymtTTL,
sum(HCOM$) as CommTTL,
sum(HPIF#) as PIF,
sum(HCLI#) as WithDrawn#,
sum(HCLI$) as WithDrawn$,
sum(HCLA#) as Closed#,
sum(HCLA$) as Closed$,
sum(HPMT$)/sum(HPL$) as Recovered,
sum(HAC#) as Active#,
sum(HAC$) as Active$


From SCDATA.SCCLNT
  Inner Join SCDATA.SCPLHS On SCDATA.SCPLHS.HCLNT = SCDATA.SCCLNT.CCLNT And
    (SCDATA.SCPLHS.HYEAR Between 17 And 17) and
(SCDATA.SCPLHS.HMONTH Between 01 And 10 )

Where SCDATA.SCCLNT.CLTGRP Like 916500
Group By     SCDATA.SCPLHS.HYEAR ,
             SCDATA.SCPLHS.HMONTH,
SCDATA.SCCLNT.CCLNT,
SCDATA.SCCLNT.CADD1,
SCDATA.SCCLNT.CADD2,
SCDATA.SCCLNT.CZIP,
SCDATA.SCCLNT.CLTGRP,
SCDATA.SCCLNT.CCITY,
SCDATA.SCCLNT.CSTATE

How can I collate this date so that my results show each date only once, and the sum of all data for that date?

Than you.

--EDIT--
Here are the results I am getting from the current query, tab delimited:
https://drive.google.com/open?id=0BwJ_JKr6NhYJVnNIVDcyNW9WMms CSV File

The results I need are:
https://drive.google.com/open?id=0BwJ_JKr6NhYJUTBDUTlDV00yanc

Answer 1

When I was grouping the query results I was including the SCCLNT column in the group. Each client group has multiple client numbers, which was causing the query to return multiple results, one for every client ID.

Answer 2

You statement has an aggregation problem - your GROUP BY includes two separate tables, the columns of which are not going to share an index (and quite possibly one of the tables may not have an index over the used columns at all). The statement is going to be slower than it needs to be.

You may find it more faster to do the aggregation just on the actual sale data:

SELECT Client.group, Client.name, Client.address1, Client.address2,
       Historical.month || '-' || Historical.year as endDate
       Historical.placed#, Historical.placed$,
       Historical.pymtMth,
       Historical.pymtTTL, Historical.commTTL,
       Historical.PIF,
       Historical.withdrawn#, Historical.withdrawn$,
       Historical.closed#, Historical.closed$,
       Historical.recovered,
       Historical.active#, Historical.active$
FROM (SELECT SCPlHs.hYear as year, 
             SCPlHs.hMonth as month,
             SUM(SCPlHs.hPl#) as placed#,
             SUM(SCPlHs.hPl$) as placed$,
             SUM(SCPlHs.hpmt$m) as pymtMth,
             SUM(SCPlHs.hPmt$) as pymtTTL,
             SUM(SCPlHs.hCom$) as commTTL,
             SUM(SCPlHs.hPif#) as PIF,
             SUM(SCPlHs.hCli#) as withdrawn#,
             SUM(SCPlHs.hCli$) as withdrawn$,
             SUM(SCPlHs.hCla#) as closed#,
             SUM(SCPlHs.hCla$) as closed$,
             SUM(SCPlHs.hPmt$) / SUM(SCPlHs.hpl$) as recovered,
             SUM(SCPlHs.hAc#) as active#,
             SUM(SCPlHs.hAc$) as active$
      FROM SCData.SCPlHs
      JOIN (SELECT DISTINCT cClnt as client
            FROM SCData.SCClnt
            WHERE SCClnt.cltGrp = 916500) Client
      ON Client.client = SCPlHs.hClnt
      -- dates, like all positive, contiguous-range types,
      -- should be queries with an exclusive upper bound.
      -- You should stop using BETWEEN, if possible.
      WHERE SCPlHs.hYear >= 17 and SCPlHs.hYear < 18
            AND SCPlHs.hMonth >= 1 and SCPlHs.hMonth < 11
      GROUP BY SCPlHs.hYear, SCPlHs.hMonth) Historical
-- Cross joins multiply the total number of rows, but that's okay here because
-- the joined table is going to only have one row
CROSS JOIN (SELECT SCClnt.cltGrp as group
                   SCClnt.cName as name,
                   TRIM(SCClnt.cAdd1) || TRIM(SCClnt.cAdd2) as address1,
                   TRIM(SCClnt.cCity) || ', ' || TRIM(SCClnt.cState) || ' ' || TRIM(SCClnt.cZip) as address2
            FROM SCData.SCClnt
            WHERE SCClnt.cltGrp = 916500
            FETCH FIRST 1 ROW ONLY) Client
ORDER BY Historical.year, Historical.month