CODEWITHSUNDEEP
sql-servert-sql
navbingo
navbingo

Reputation: 223

How do you create a summary column?

Here is my current SQL code,

  select   
   coalesce(cast(machinename as varchar(28)), 'Grand Total:') as 'machinename', 
  (IsNull(cast(CRATE_SMALL / 60 as varchar(24)),'0') + ':' +  IsNull(cast(CRATE_SMALL % 60 as varchar(24)),'0') ) as '1001' ,
  (IsNull(cast(CRATE_MEDIUM / 60 as varchar(24)),'0') + ':' +  IsNull(cast(CRATE_MEDIUM % 60 as varchar(24)),'0'))as '1002',   
  (IsNull(cast(NO_SCHEDULE / 60 as varchar(24)),'0') + ':' +  IsNull(cast(NO_SCHEDULE % 60 as varchar(24)),'0'))   as '9999' 
     from    ( 
    select  machinename  ,   
    sum(case when vfrm.job_id = '1001' then DateDiff(mi, 0, total_time)  end) as  CRATE_SMALL ,       
    sum(case when vfrm.job_id = '1002' then DateDiff(mi, 0, total_time)  end) as  CRATE_MEDIUM ,       
     sum(case when vfrm.job_id = '9999' then DateDiff(mi, 0, total_time)  end) as  NO_SCHEDULE
    from    ven_fullreportmaster vfrm
             INNER JOIN ven_descriptionmaster VDM ON VDM.description_id = vfrm..description_id
                    inner join ven_machinemaster vm on  vm.machine_id = vfrm..machine_id
                      where  vfrm.entry_date = convert(varchar, getdate()-7, 105)  
                      and  vfrm.shift_id =1 
                      and vfrm.is_task_completed ='Y' 
    group by   machinename  with rollup
        ) as SubQueryALias

the output :

 machinename    1001    1002        9999

  ARISTECH    0:0      0:0        10:0

  FADAL    0:0     5:0        10:0

  Grand Total:  0:0   5:0   20:0

problem:

is there a anyway to show only the column(s) whose total is greater than zero... So, in the above example I dont want to show the column name '1001'.

Upvotes: 0

Views: 600

Answers (4)

saga
saga

Reputation: 61

how about use a temp table to store the data query out and then build the output data from the temp table?

just

1.{your select query} into #t {your from where query}

2.select entry_date, machinename, [1001], [1002], [9999] from #t union select '' as entry_date, 'total', sum([1001]), sum([1002]), sum([9999]) from #t

the logic is more clear with these steps, however, you can also use similar subquery to get the same result

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

To add the totals row, you ought to look at the ROLLUP part of the GROUP BY clause. This can produce sub-totals as well as the grand total, depending on what your final requirements are.

For hiding a column, there's not a solution for that in tsql - a SELECT statement always produces result sets with the same shape (names and types of columns).

But both may be better served by a reporting tool, if that's where this data is going (which I expect it is, given the nature of the query). Reporting tools tend to have better post-processing facilities.

Upvotes: 1

Jamiec
Jamiec

Reputation: 136074

In all honesty, you shouldn't. This is a display issue, and as such should be dealt with when you display the data, not retrieve it from the database.

Reports, and datagrids and the like generally have functionality to do exactly this. Perhaps give more info about how you are displaying the data and someone might be able to provide more info.

Upvotes: 2

xyz
xyz

Reputation: 1

try this:

select
    entry_date,  machinename, [1001], [1002], [9999], [1001]+[1002]+[9999] as Total
    FROM ( --your query here

         ) d
    WHERE [1001]+[1002]+[9999]>0

Upvotes: 0

Related Questions