x

Totals in SQL query

i have a output like this:

 VALUE        TOTAL                             PAY_HEAD_MASTER_ID
 ------------ --------------------------------- ------------------
      1000.00                           1000.00 PH00000007
      1000.00                           1000.00 PH00000007
      3000.00                           3000.00 PH00000007
         0.00                              0.00 PH00000008
         0.00                              0.00 PH00000008
         0.00                              0.00 PH00000008
       250.00                            250.00 PH00000008
       250.00                            250.00 PH00000008
       750.00                            750.00 PH00000008

My desired output is :

 VALUE        TOTAL                             PAY_HEAD_MASTER_ID     TOTALS
 ------------ --------------------------------- ------------------ -----------------------
      1000.00                           1000.00 PH00000007
      1000.00                           1000.00 PH00000007
      3000.00                           3000.00 PH00000007             5000
         0.00                              0.00 PH00000008
         0.00                              0.00 PH00000008
         0.00                              0.00 PH00000008
       250.00                            250.00 PH00000008
       250.00                            250.00 PH00000008
       750.00                            750.00 PH00000008             1250

Plz anyone help!!

more ▼

asked Apr 28, 2011 at 03:59 AM in Default

avatar image

sagar 2
111 8 8 10

select
coalesce(D.value,0.00) as VALUE, SUM(D.VALUE) OVER (PARTITION BY B.PAY_HEAD_MASTER_ID) AS TOTAL from hr_pay_head A, hr_pay_head_master B, HR_PAY_EMPLOYEE_PAYSLIP_HISTORY C, HR_PAY_EMPLOYEE_PAYSLIP_HISTORY_DETAILS D, HR_DESIGNATION HD, hr_branch_department_view DV

where A.PAY_HEAD_ID=D.PAY_HEAD_ID and D.PAYSLIP_HISTORY_ID=C.PAYSLIP_HISTORY_ID and A.PAY_HEAD_MASTER_ID=B.PAY_HEAD_MASTER_ID and C.from_date='04-01-2010' and C.to_date='04-30-2010' and B.nature=-1 and B.active_yn='Y' AND DV.BRANCHNAME='M' and HD.DESIGNATION_NAME='Ma' and DV.DEPARTMENTNAME='DEPT'

group by D.VALUE, B.PAY_HEAD_MASTER_ID, C.employee_id

order by B.PAY_HEAD_MASTER_ID

I want the other rows to be blank

Apr 28, 2011 at 05:52 AM sagar 2
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

you need to use the SUM function with a windowing parameter. Here is an example based on AdventureWorks

 SELECT  [st].[TerritoryID] ,
     [st].[Name] ,
     [st].[CountryRegionCode] ,
     st.[group],
     COUNT(*) OVER ( PARTITION BY st.[Group] ) AS [GroupCount],
     COUNT(*) OVER ( PARTITION BY st.countryregioncode ) AS [CountryCount] ,
     COUNT(*) OVER ( PARTITION BY st.name ) AS [TerritoryCount]
 FROM    [Sales].[SalesTerritory] AS st
 ORDER BY [st].[Group],[st].[CountryRegionCode]

You need to use the SUM function rather than COUNT and partition by the column of your choosing

more ▼

answered Apr 28, 2011 at 04:06 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

I did something like this: SUM(VALUE) OVER (PARTITION BY PAY_HEAD_MASTER_ID) as SUBTOTAL,But i m getting ouput like this:

VALUE PAY_HEAD_MASTER_ID SUBTOTAL


  1000.00 PH00000007                                   5000.00
  1000.00 PH00000007                                   5000.00
  3000.00 PH00000007                                   5000.00
     0.00 PH00000008                                   1250.00
     0.00 PH00000008                                   1250.00
     0.00 PH00000008                                   1250.00
   250.00 PH00000008                                   1250.00
   250.00 PH00000008                                   1250.00
   750.00 PH00000008                                   1250.00
     0.00 PH00000009                                    500.00
     0.00 PH00000009                                    500.00
     0.00 PH00000009                                    500.00
   100.00 PH00000009                                    500.00
   100.00 PH00000009                                    500.00
   300.00 PH00000009                                    500.00


Apr 28, 2011 at 04:28 AM sagar 2

yes, that's what I would expect. If you want a single line with your total then the SQL will be more complex, less efficient and I would suggest ought to be done in the presentation layer rather than TSQL

Apr 28, 2011 at 04:37 AM Fatherjack ♦♦

This is exactly what @Fatherjack was suggesting - what do you want to see in the column 'Subtotal' - do you wish this to be blank for all but the last row for that pay_head_master_id? - if so that's a presentation level concern

Apr 28, 2011 at 04:39 AM Kev Riley ♦♦

may be better to show the query you are using, rather than just say my output is like this, but I want it like that, what's the magic keyword?

Apr 28, 2011 at 04:40 AM Kev Riley ♦♦

@Kev Riley - is it Plz?

Apr 28, 2011 at 04:41 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

I agree with Fatherjack's suggestion to handle the show/hide Total issue in the presentation layer. However, if you do insist to do it in T-SQL, and assuming that you want the Total to appear on each last line of your "records per head master" group, give the following example a try:

 create table t
 (
     value decimal(10,2),
     PAY_HEAD_MASTER_ID varchar(20)
 );
 --set up some test values
 insert into t values 
     (1000.00, 'PH00000007'),
     (1000.00, 'PH00000007'),
     (3000.00, 'PH00000007'),
        (0.00, 'PH00000008'),
        (0.00, 'PH00000008'),
      (250.00, 'PH00000008'),
      (250.00, 'PH00000008'),
      (750.00, 'PH00000008');
 
 --THE QUERY
 with MyData as
 (
     select value, PAY_HEAD_MASTER_ID,
         SUM(value) OVER (PARTITION BY PAY_HEAD_MASTER_ID) as Total,
         ROW_NUMBER() OVER (PARTITION BY PAY_HEAD_MASTER_ID ORDER BY PAY_HEAD_MASTER_ID) as RowNumberPerHeadMaster,
         COUNT(value) OVER (PARTITION BY PAY_HEAD_MASTER_ID) as CountPerHeadMaster
     from t
 )
 select value, PAY_HEAD_MASTER_ID,
     CASE WHEN RowNumberPerHeadMaster = CountPerHeadMaster then Total else NULL end as Total
 from MyData;
 
 --cleanup
 drop table t;
more ▼

answered May 04, 2011 at 04:58 AM

avatar image

Valentino Vranken
1.5k 2 4 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1131

asked: Apr 28, 2011 at 03:59 AM

Seen: 907 times

Last Updated: Apr 28, 2011 at 04:04 AM

Copyright 2017 Redgate Software. Privacy Policy