Need to total/SUM by another column condition

[Pasi]

HI,

I have a query in which works fine but I am trying to get the total for company 01 an company 15 and so on, right now its group by company how can I create another column to total /SUM them by company?

with cte as
(SELECT distinct AL_AMOUNT,CM.PAYGRP,MEMO,JO.COMPANY,CM.EMPID,CV.PAY_END_DT

FROM pay_CHK CM

join CHECKS CV on CV.EMPID =CM.EMPID

JOIN JOBs JO ON JO.PAYGRP=CM.PAYGRP
AND JO.EMPID=CM.EMPID
AND JO.EFDT=(SELECT MAX(EFDT)
FROM JOBs
WHERE EMPID=JO.EMPID
AND EFDT<=CM.CHECK_DT
)
where memo in('401')

  			)

SELECT SUM(AL_AMOUNT) AS TOTAL_AMOUNT, CM.PAYGRP,MEMO,COMPANY,PAY_END_DT

FROM cte CM
where pay_end_dt='11/30/18'
group BY CM.PAYGRP,company, MEMO,PAY_END_DT

[yosiasz]

Then you will need to remove PAYGRD

[harishgg1]

this is the clue

sum(total_amount) OVER(partition by company order by company)

please let me know if you want the whole SQL
thanks

[Pasi]

No I need the PAYGRP.

[Pasi]

Thanks I tried it but I get this, looks like it adds it to itself?

this is the code:

SELECT sum(AL_AMOUNT) OVER(partition by company order by company) AS TOTAL_AMOUN,PAYGRP,MEMO,COMPANY,PAY_END_DT

[harishgg1]

Can you please explain what output you are looking for
All sorts of things are possible
Depends on what you are looking for

You are looking for
Something like this
I mean example output

Company TotalAmount
15 12677
01 10600

[Pasi]

Yes I want to add all companies 01, 15.. etc.. together to get one SUM total . right now there are 2 SUM for Company 1 and 15.. Thanks.

like Company 01 = SUM+SUM or total_amount+total_amount.

[yosiasz]

You cant bcs you have pay grade. It is summing by company then by paygrade

[harishgg1]

Ok

Please try this and let me know
Thank you

Select company , sum ( al_amount )
From
cte CM
Group by company

If you want output like this
Company TotalAmount
15 23400
01 44600

Select sum ( al_amount )
From
cte CM

If you want output like this
Total amount
102800

[Pasi]

Thanks yosiasz, yes you right I cant do it and I need to have all those other columns in report and in group by.

[Pasi]

Thanks harish, I can't do it because I need all the columns and those columns in group by. if I had only the Company, it could have been done. I thought may be there is a way to do this asking all the Gurus here.

[yosiasz]

another way you could do it is if you had the summation done in a subquery. but since you did not provide any sample data this might or might not work

SELECT TOTAL_AMOUNT, CM.PAYGRP,MEMO,cm,COMPANY,PAY_END_DT

FROM cte CM
join (select SUM(AL_AMOUNT) AS TOTAL_AMOUNT , company
           from cte  where pay_end_dt='11/30/18' group BY company) s on cm.company = s.company
where pay_end_dt='11/30/18'

[Pasi]

Thanks Yosiaz, its not working. I have to do this in the report itself. your solution gives me so many rows for each person. sorry I cant provide data. Thanks again!

[harishgg1]

hi pasi

basically you have data like this

multiple rows

How do you want the data in 1 row ????
because the PAYGRP is different .. It comes as two rows..

How do you want the data in 1 row
Please give us some idea
or Example

We can do SQL according to that !!!

we can also make it like this .. the paygrp combined

[Pasi]

Thanks harish, yes just like above pics in one row and paygrp ... looks like you have the data do you still need it? How do I attach? I don't see the link to attach?
Pasi

[harishgg1]

hi

i tried to do it

please check and let me know .. thanks

drop create sample data Only 2 rows

use tempdb 

go 


drop table data
go 

create table data
(
total_amount decimal(10,2) NULL, 
company   int NULL,
paygrp varchar(100) NULL,
memo   int NULL,
pay_end_dt date NULL
)
go

insert into data  select 349135.17,15,'SA3',401,'2018-11-30'
insert into data  select 413346.11,15,'SA4',401,'2018-11-30'
go

select * from data
go

SQL .. comma seperated paygrp

SELECT sumtot_amt, 
       company, 
       Stuff((SELECT ',' + paygrp 
              FROM   data 
              WHERE  company = t.company 
                     AND memo = t.memo 
                     AND pay_end_dt = t.pay_end_dt 
              FOR xml path('')), 1, 1, ''), 
       memo, 
       pay_end_dt 
FROM   (SELECT company, 
               memo, 
               pay_end_dt, 
               Sum(total_amount) AS sumtot_amt 
        FROM   data 
        GROUP  BY company, 
                  memo, 
                  pay_end_dt) t

Data

Comma Seperated paygrp Results

[Pasi]

Thanks Harsh! Almost there. I am getting right Totals but paygrp are adding correctly but repeating??

Thanks,
Pasi

[Pasi]

Never mind, its fixed had to add "DISTINCT" to select..! Thanks so much!

SELECT DISTINCT sumtot_amt,
company,
Stuff((SELECT ',' + paygrp
FROM data
WHERE company = t.company
AND memo = t.memo
AND pay_end_dt = t.pay_end_dt
FOR xml path('')), 1, 1, ''),
memo,
pay_end_dt

One thing, is there any easier way to do this with out STUFF and 2 select statments, like one line code?

[yosiasz]

SELECT company, 
        memo, 
Stuff((SELECT distinct ',' + paygrp 
              FROM   #data 
              WHERE  company = t.company 
                     AND memo = t.memo 
                     AND pay_end_dt = t.pay_end_dt 
              FOR xml path('')), 1, 1, '')
        pay_end_dt, 
        Sum(total_amount) AS sumtot_amt 
FROM   #data t
GROUP  BY company, 
            memo, 
            pay_end_dt

[Pasi]

Thanks Yosiaz. its better.
Pasi