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
mgmhicksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
0
mgmhicksAuthor Commented:
seems to be erroring on with myquery using sql 2008 not sure about "with"
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
"With" was introduced in SQL 2008 - see https://msdn.microsoft.com/en-GB/library/ms175972.aspx

Try this:

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

If that doesn't work, can you confirm this runs:

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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mgmhicksAuthor Commented:
thank you that one worked.
0
PortletPaulfreelancerCommented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.