CODEWITHSUNDEEP
sqlsql-server-2005sum
user1086159
user1086159

Reputation: 1055

SQL SUM confusion

I have run the below query for my data:-

SELECT     CONTRACTNAME,
           AccountNo,
           TransDate, 
           JournalNo,
           AuditTrail,           
           Description,       
           CONTRACTNUMBER,
           GROUPING(CONTRACTNUMBER) as CONTRACTNUMBER,   
           SUM(PAFeebillamount)as Total  
FROM       SRVS.dbo.BTQGLDistributionsWithProjectCodesHM
WHERE      TransDate > '2012' AND CONTRACTNUMBER <> '00900'
GROUP BY   CONTRACTNAME,  
           AccountNo,   
           TransDate,
           JournalNo, 
           AuditTrail,    
           Description,      
           CONTRACTNUMBER with rollup  
ORDER BY   GROUPING (CONTRACTNUMBER)

this gives me the results:-

enter image description here

how can I change this so I get a single line Total for each contract number. So it looks like the below example:-

enter image description here

at this moment in time I am not bothered about the accnt no / journal no etc as it is only going to be a summary.

Just looking for advice on best way to sum up my groups by contract number?

Upvotes: 0

Views: 75

Answers (3)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

SELECT     CONTRACTNAME,
           '' AccountNo,
           '' TransDate, 
           '' JournalNo,
           '' AuditTrail,           
           '' Description,       
           CONTRACTNUMBER,
           GROUPING(CONTRACTNUMBER) as CONTRACTNUMBER,   
           SUM(PAFeebillamount)as Total  
FROM       SRVS.dbo.BTQGLDistributionsWithProjectCodesHM
WHERE      TransDate > '2012' AND CONTRACTNUMBER <> '00900'
GROUP BY   CONTRACTNAME,                  
           CONTRACTNUMBER with rollup  
ORDER BY   GROUPING (CONTRACTNUMBER)

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Since you arn't bothered about what to select for AccountNo, TransDate, etc, you can use MAX as an aggregate function something like:

SELECT
  CONTRACTNAME, 
  MAX(AccountNo), 
  MAX(TransDate), 
  MAX(JournalNo),
  MAX(AuditTrail),
  MAX(Description),
  CONTRACTNUMBER, 
  GROUPING(CONTRACTNUMBER) as CONTRACTNUMBER, 
  SUM(PAFeebillamount)as Total
FROM SRVS.dbo.BTQGLDistributionsWithProjectCodesHM 
WHERE TransDate > '2012' 
  AND CONTRACTNUMBER <> '00900' 
GROUP BY 
  CONTRACTNAME,
  CONTRACTNUMBER with rollup
ORDER BY GROUPING (CONTRACTNUMBER)

And If you don't want them just remove them from the SELECT statement, or replace them with NULL or '':

SELECT
  CONTRACTNAME,
  CONTRACTNUMBER, 
  GROUPING(CONTRACTNUMBER) as CONTRACTNUMBER, 
  SUM(PAFeebillamount)as Total
FROM SRVS.dbo.BTQGLDistributionsWithProjectCodesHM 
WHERE TransDate > '2012' 
  AND CONTRACTNUMBER <> '00900' 
GROUP BY 
  CONTRACTNAME,
  CONTRACTNUMBER with rollup
ORDER BY GROUPING (CONTRACTNUMBER)

Upvotes: 0

jzworkman
jzworkman

Reputation: 2703

SELECT CONTRACTNAME, CONTRACTNUMBER, grouping(CONTRACTNUMBER) as CONTRACTNUMBER
SUM(PAFeebillamount)as Total
FROM SRVS.dbo.BTQGLDistributionsWithProjectCodesHM 
WHERE TransDate > '2012' AND CONTRACTNUMBER <> '00900' 
GROUP BY 
  CONTRACTNAME,
  CONTRACTNUMBER with rollup
ORDER BY GROUPING (CONTRACTNUMBER)

This should give you a total for each contract you have. The reason you get multiple results is because you are grouping on the contractName, Acct, Date, JournalNo, AuditTrail, and Desc. If you want a complete total(one for each contract number) then you need to be grouping just on the contract number.

Upvotes: 1

Related Questions