Summarizing Total Amount

Transact-SQL
1

HI ,

I am working on a query and trying to get to summarize total_amount but still don't get the right group by, it is not summarizing by group. Like PYGRP has all SA3 and its not group and I get too many rows I think my query needs a tweak and I think my SUM is not in the right place.

There should only be one SA3 with total_amount with one company, one Cd, one week_nbr and so on..
Thanks,
Pasi.

Select sum(al_amount) as TOTAL_AMOUNT, PAYGP,COMPANY, CD,Week_nbr, Payroll
from CHECKS B
inner join JOB JO ON JO.PAYGP=B.PAYGP
WHERE B.EMPLID=JO.EMPLID
and CD IN ('401','40A','40P','410','411')
AND ENTRY_NBR <>0
AND JO.FDT=(SELECT MAX(FDT)
FROM JOB
WHERE EMPLID=JO.EMPLID
AND FDT<=B.CHECK_DT
)
group by b.PAYGP,company, CD, b.EMPLID, week_nbr, Payroll

image

2

could you please try this

select SUM(TOTAL_AMOUNT),PAYGP,COMPANY,CD,Week_nbr,Payroll from(
Select sum(al_amount) as TOTAL_AMOUNT, PAYGP,COMPANY, CD,Week_nbr, Payroll
from CHECKS B
inner join JOB JO ON JO.PAYGP=B.PAYGP
WHERE B.EMPLID=JO.EMPLID
and CD IN ('401','40A','40P','410','411')
AND ENTRY_NBR <>0
AND JO.FDT=(SELECT MAX(FDT)
FROM JOB
WHERE EMPLID=JO.EMPLID
AND FDT<=B.CHECK_DT
)
group by b.PAYGP,company, CD, b.EMPLID, week_nbr, Payroll
[/quote] ) c
group by PAYGP,COMPANY, CD,Week_nbr, Payroll

3

hi

please provide data script
like

drop table
create table abc
(
col1 int ,
col2 varchar(100)
)
insert into abc select 1,'har'
insert into abc select 2,'pra'

4

There is not Drop table and create table.
Pasi

5

Thanks Mannesravya, I am not sure what your trying to do here? its not working? your version is convoluted.
Pasi

6

i mean if we have the data to work with

we can give solution ..

:slight_smile:

1 Like
7 
Select 
	sum(TOTAL_AMOUNT) as TOTAL_AMOUNT, 
	PAYGP,
	COMPANY, 
	CD,Week_nbr,
	Payroll
FROM (
			Select 
				sum(al_amount) as TOTAL_AMOUNT, 
				PAYGP,
				COMPANY, 
				CD,Week_nbr,
				Payroll
			from CHECKS B
			inner join JOB JO ON JO.PAYGP=B.PAYGP
			WHERE B.EMPLID = JO.EMPLID
			and CD IN ('401','40A','40P','410','411')
			AND ENTRY_NBR <> 0
			AND JO.FDT=(	SELECT MAX(FDT)
							FROM JOB
							WHERE EMPLID=JO.EMPLID
							AND FDT<=B.CHECK_DT
						)
			group by b.PAYGP,company, CD, b.EMPLID, week_nbr, Payroll
)WRAP
group by b.PAYGP,company, CD, b.EMPLID, week_nbr, Payroll
8

I suspect you need to look into GROUPING SETS. If you provided some sample data (as DDL/DML so we can just use it in SMSS) and expected output, it would be a lot easier to help.

9

Thanks AndyC, this is good info, I will tweak it and it probably will work.
Pasi

10

Thanks Muj9 will try your tips .
Pasi