Having SUM and GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Having SUM and GROUP BY

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-15 : 15:27:46

I need to calculate the sum of 2 variables, bp.gross_quantity and bp.net_quantity. The way I have my query set up I need to add all the other fields in the select as part of the "group by", which will not render the result I need. I need the sum to be by same bm.reference_number only. Is this doable by using a sub query insted?

SELECT	convert(char(1), bm.type) as record_id,  
                                                               
        case when sp.petroex_company_code IS Null then ' ' 
         else sp.petroex_company_code
        end as supplier_code,  
      
        case when convert(char(2), month(bm.movement_date)) IS NULL THEN '' 
         else convert(char(2), month(bm.movement_date)) 
        end as movement_month,
        
        case when convert(char(4), year(bm.movement_date)) IS NULL THEN '' 
          else convert(char(4), year(bm.movement_date)) 
        end as movement_year, 
       
        case when convert(char(8),replace(convert(char(10), bm.movement_date, 101), '/', '')) IS NULL THEN ''
         else convert(char(8), year(bm.movement_date))
        end as movement_date,

       case when bm.remarks IS Null then ' '  
         else bm.remarks
        end as trans_descr,

        case when bm.reference_number IS Null then ' '  
         else bm.reference_number 
        end as ticket_no,

        case when tp.petroex_product_code IS Null then ' ' 
          else tp.petroex_product_code 
        end as petroex_code, 
                                                    
        case when bp.tank_code IS Null then ' ' 
          else bp.tank_code 
        end as tank_code,                                                                                
          
        case when convert(char(10), replace(convert(decimal(10,2), sum(bp.gross_quantity / 42.0)), '.', '')) IS NULL THEN ''
         else convert(char(10), replace(convert(decimal(10,2), sum(bp.gross_quantity / 42.0)), '.', ''))
        end as qty_gross,

        case when convert(char(11), replace(convert(decimal(10,2), sum(bp.net_quantity / 42.0)), '.', '')) IS NULL THEN ''
         else convert(char(11), replace(convert(decimal(10,2), sum(bp.net_quantity / 42.0)), '.', ''))
        end as qty_net

from   [VEMA-FACS1].FUELFACS.dbo.bulk_movement as bm
       inner JOIN [VEMA-FACS1].FUELFACS.dbo.bulk_product AS bp ON bp.bulk_movement_id = bm.bulk_movement_id  
       inner JOIN [VEMA-FACS1].FUELFACS.dbo.terminal_product AS tp ON tp.terminal_product_number = bp.terminal_product_number
       inner JOIN [VEMA-FACS1].FUELFACS.dbo.supplier AS sp ON sp.supplier_number = bp.supplier_number
where (@DateFrom IS Null OR bm.movement_date >=  @DateFrom) and
      (@DateTo IS Null OR bm.movement_date <=  @DateTo) and
      (bm.type = 1 or bm.type = 2) and
       bm.status > 4 and
       tp.product_group_code <> 'ADD'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-15 : 15:31:09

I think you can just add
sum(bp.gross_quantity + bp.net_quantity) over(partition by bm.movement_number )

Jim

Everyday I learn something that somebody else already knew

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-15 : 15:52:44

Tried this, but still renders single lines for same bm.movement_number.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-04-16 : 23:28:08

bm.movement_number - nice choice of a table alias... :)

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-04-17 : 03:01:13

probably you could post some sample data and show what you're expecting so that its easier for somebody to suggest you a solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-17 : 06:04:13

[code]SELECT CONVERT(CHAR(1), bm.type) AS record_id,
COALESCE(sp.petroex_company_code, ' ') AS supplier_code,
COALESCE(CONVERT(CHAR(2), MONTH(bm.movement_date)), ' ') AS movement_month,
COALESCE(CONVERT(CHAR(4), YEAR(bm.movement_date)), ' ') AS movement_year,
COALESCE(REPLACE(CONVERT(CHAR(10), bm.movement_date, 101), '/', ''), ' ') AS movement_date,
COALESCE(bm.remarks, ' ') AS trans_descr,
COALESCE(bm.reference_number, ' ') AS ticket_no,
COALESCE(tp.petroex_product_code, ' ') AS petroex_code,
COALESCE(bp.tank_code, ' ') AS tank_code,
COALESCE(STR(100E * SUM(bp.gross_quantity / 42E) OVER (PARTITION BY bm.reference_number), 10, 0), ' ') AS qty_gross,
COALESCE(STR(100E * SUM(bp.net_quantity / 42E) OVER (PARTITION BY bm.reference_number), 11, 0), ' ') AS qty_net
FROM [VEMA-FACS1].FUELFACS.dbo.bulk_movement AS bm
INNER JOIN [VEMA-FACS1].FUELFACS.dbo.bulk_product AS bp ON bp.bulk_movement_id = bm.bulk_movement_id
INNER JOIN [VEMA-FACS1].FUELFACS.dbo.terminal_product AS tp ON tp.terminal_product_number = bp.terminal_product_number
AND tp.product_group_code <> 'ADD'
INNER JOIN [VEMA-FACS1].FUELFACS.dbo.supplier AS sp ON sp.supplier_number = bp.supplier_number
WHERE (@DateFrom IS NULL OR bm.movement_date >= @DateFrom)
AND (@DateTo IS NULL OR bm.movement_date <= @DateTo)
AND bm.type IN (1, 2)
AND bm.status > 4[/code]

N 56°04'39.26"
E 12°55'05.63"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-18 : 14:34:43

Using propsed query string, I am getting very close to what I need. The query lists the same movement number 3 times where it should be only one time (summary by movement_no) , the quantity is however correct as it i adding up (minus and plus).

COALESCE(STR(100E * SUM(bp.gross_quantity / 42E) OVER (PARTITION BY bm.movement_number), 10, 0), '          ') AS qty_gross,
COALESCE(STR(100E * SUM(bp.net_quantity / 42E) OVER (PARTITION BY bm.movement_number), 11, 0), '           ') AS qty_net,

Result:

ticket_no	movement_no	petroex_code	qty_gross	qty_net
	
MC 01 497	2191		D87	  	883100	    	859700
MC 01 497	2191		D87	    	883100	       	859700
MC 01 497	2191		D87		831000	    	859700

They all have same movement number but listed 3 times


bulk_movement table:

movement_id	movement_no

54332		2191	 
54355		2191	 
54356		2191	 


bulk_product table:

movement_id	movement_no		gross_qty	net_qty

54332		TK_17	1		 8831		 8597
54355		TK_17	1		-8831		-8597
54356		TK_17	1		370902		361074

The only link between bulk_movement table and bulk_product table is the movement_id field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources