asked on

How to create group on sql statement

I have 2 tables on need to union together. 1 table I get the information grouped by a column called glperiod ie(201503).

I sum the count and the amount of each distinct period for a total of 12 periods. Here is the sql for that

-- Insert statements for procedure here
--	 select x.GLPeriod,x.PaymentType,x.count,x.amt from (SELECT DISTINCT GLPeriod, PaymentType,COUNT(*) as count, SUM(TransAmt) as amt 
--FROM         TransactionHeader
--WHERE     (PropertyId = @PropID) AND (TransHeaderUserStamp = 'eDex' or TransHeaderUserStamp='MGM') AND (PaymentType IS NOT NULL) AND (GLPeriod >= @GlPeriod) and TransDesc like 'WEB%' and PaymentType='P'
--GROUP BY GLPeriod, PaymentType) as x

Open in new window

I end up with this

GLPeriod      PaymentType      count      amt
201402      P      15      12933.57
201403      P      16      12187.9
201404      P      17      12556.34
201405      P      20      16028.33
201406      P      17      12780
201407      P      12      8880.5
201408      P      18      15195.39
201409      P      13      11151.5
201410      P      18      12832.35
201411      P      11      8627
201412      P      26      17194.95
201501      P      22      13392.58
201502      P      18      14361.28
201503      P      5      3954

so what I need to do is union another file, that doesn't have a glperiod column it has a transdate column. So need to group the records like above only I don't have the glperiod column. This is what I started but not working for me.

select y.glperiod,y.paymenttype,y.count,y.amt from (select distinct cast(month(transdate)+ year(transdate) as nvarchar(10)) as glperiod, 'C' as paymenttype,
(select COUNT(*) from tag_edex.dbo.TAG_AchBatches where CAST(transdate as DATE)>=@begin and CAST(transdate as DATE)<=@end and PropertyID=@PropID)as count,
(Select SUM(totalamt) from tag_edex.dbo.TAG_AchBatches where CAST(transdate as DATE)>=@begin and CAST(transdate as DATE)<=@end and PropertyID=@PropID) as amt 
from TAG_eDex.dbo.TAG_AchBatches) as  y

Open in new window

so what I would end up with is a dataset with 2 rows for each glperiod, over the last 12 to 13 months.

thanks in advance

Phillip Burton

with myquery as (select convert(char(4),year(transdate)) + case when month(transdate)<10 then '0' + convert(char(1),month(transdate)) else convert(char(2),month(transdate)) end as glperiod, 'C' as paymenttype, totalamt
from tag_edex.dbo.TAG_AchBatches where PropertyID=@PropID) as amt)
Select glperiod, paymenttype, count(*) as mycount, sum(totalamount) as totalamount
from myquery
group by glperiod, paymenttype

mgmhicks

ASKER

seems to be erroring on with myquery using sql 2008 not sure about "with"

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mgmhicks

ASKER

thank you that one worked.

PortletPaul

nb
WITH is not required for this query and there is NO advantage in that query by using it

Instead, and this is supported in all SQL dbms's and versions, just use a "derived table"
(or this can be called "nested subquery")

SELECT glperiod, paymenttype, COUNT(*) AS mycount, SUM(totalamount) AS totalamount
FROM (
        SELECT
          CONVERT(char(4), YEAR(transdate)) + CASE
            WHEN MONTH(transdate) < 10 THEN '0' + CONVERT(char(1), MONTH(transdate))
            ELSE CONVERT(char(2), MONTH(transdate))
          END AS glperiod,
          'C' AS paymenttype,
          totalamt
        FROM tag_edex.dbo.TAG_AchBatches
        WHERE PropertyID = @PropID
    ) AS myquery
GROUP BY glperiod,
         paymenttype
;

Open in new window

The equivalent using WITH

WITH myquery AS (
        SELECT
          CONVERT(char(4), YEAR(transdate)) + CASE
            WHEN MONTH(transdate) < 10 THEN '0' + CONVERT(char(1), MONTH(transdate))
            ELSE CONVERT(char(2), MONTH(transdate))
          END AS glperiod,
          'C' AS paymenttype,
          totalamt
        FROM tag_edex.dbo.TAG_AchBatches
        WHERE PropertyID = @PropID
        )
SELECT glperiod, paymenttype, COUNT(*) AS mycount, SUM(totalamount) AS totalamount
FROM myquery
GROUP BY glperiod,
         paymenttype
;

Open in new window

Where Common Table Expressions (CTEs) are useful is:
a. when you need recursion
b. you are able to avoid repetition (of a subquery usually)

there is NO advantage in a CTE otherwise (and in fact in some databases using a CTE is considered a costly overhead (eg.. in Oracle CTEs consume valuable shared memory space)