Query for summation up to certain rows in a table

Question

I have a query like:

select
    CONVERT(VARCHAR(7),[startdatetime],111) AS [year-month],
    nm.nameLine1,
    sum(datediff(hour, startdatetime, enddatetime)) as total 
from  srl
inner join  sr on srl= sr.ServiceRequestId
inner join Name nm on(sr.clientCustomerId = nm.customerId and nm.nameTypeId = 'OFICE')
where (startdatetime >= '08-01-2011 00:00:00.000' and enddatetime <= '10-31-2011 00:00:00.000')
group by nm.nameLine1, [year-month]
order by nm.nameLine1, [year-month]

output of the above query is::

year-month nameLine1       total       
---------- ---------       ----------- 
2011/08    B               4 
2011/09    B               7 
2011/10    B               0 
2011/08    E               167 
2011/09    E               212 
2011/10    E               131 
2011/08    L               14 
2011/09    L               23 
2011/10    L               3 
2011/08    O               18 
2011/09    O               8 
2011/10    O               7 
2011/08    S               43 
2011/09    S               60 
2011/10    S               60

Now my question is, what should I do in the query to get the summation in a different column called nameLine1total for a single nameLine1. The output should be like this:

year-month nameLine1       total            nameLine1total
---------- ---------       -----------      ---------------
2011/08    B               4         
2011/09    B               7                 
2011/10    B               0                    11 
2011/08    E               167 
2011/09    E               212 
2011/10    E               131                  510 
2011/08    L               14 
2011/09    L               23 
2011/10    L               3                    40 
2011/08    O               18 
2011/09    O               8 
2011/10    O               7                    33
2011/08    S               43 
2011/09    S               60 
2011/10    S               60                   163

score 0 · Answer 1 · answered Dec 12 '11 at 04:48

0

what variety of sql are you using - sql server, mysql, etc. Also, it doesn't matter for your answer but do you really want <= 10-31-2011 00:00:00, so that data from october 31 isn't included in your query? Also, how are you going to use that query - if it's for a reporting tool, quite likely the reporting tool can calculate it more easily than sql can. If it would be acceptable to have the nameline total included on every row, that would be easier - e.g. if all three rows for "S" said "163" in the last column, is that okay?

answered Dec 12 '11 at 04:48

Levin Magruder

1,905
18
26

:: Can i know what a reporting tool meant for... the year month is jus a sample, later i have to increase the period accordingly..therefore i prefer a concrete query so that i can use the totals even if i increase or change the year month.... – sonu Dec 12 '11 at 23:40
::if all the three rows consists the same value..there might be a chances for redundant data and performance might decrease...so better i would like to prefer a single value correspondingly... – sonu Dec 12 '11 at 23:42
Thanku so much for ur response..!! – sonu Dec 12 '11 at 23:43

score 0 · Answer 2 · answered Dec 12 '11 at 07:14

Because of convert(.., .., 111) I assume that this is a SQL Server, if it's 2005+ I would use CTE:

with cte (ym, n, total, row) as (
    select *, row_number() over(partition by nameLine1 order by nameLine1, [year-month])  from (
        select
            convert(varchar(7),[startdatetime],111) as [year-month],
            nm.nameLine1,
            sum(datediff(hour, startdatetime, enddatetime)) as total 
        from  srl
        inner join sr on srl= sr.ServiceRequestId
        inner join Name nm on 
            sr.clientCustomerId = nm.customerId and 
            nm.nameTypeId = 'OFICE'
        where startdatetime >= '08-01-2011 00:00:00.000' and enddatetime <= '10-31-2011 00:00:00.000'
        group by nm.nameLine1, [year-month]
        order by nm.nameLine1, [year-month]
    ) t
) 
select c.ym as [year-month], c.n as [nameLine1], c.total as [total], g.total as [nameLine1total]
from cte c
left join (
    select max(row) as row, sum(total) as total, n 
    from cte group by n
) g on c.n = g.n and c.row = g.row

This query is used for sybase...can u plz let me know , does this work for sybase as well....? — sonu, Dec 12 '11 at 23:36
@sonu, sorry, I never had to deal with Sybase so I can't tell you but depending on [ROW_NUMBER function](http://dcx.sybase.com/1100/en/dbusage_en11/ug-olap-s-51258147.html) and [Introduction to common table expressions](http://dcx.sybase.com/1100/en/dbusage_en11/commontblexpr-s-5414852.html) this might work in SQL Anywhere 11.0.0. — Michał Powaga, Dec 13 '11 at 06:53
Powaga : : Thanku for ur valuable comments!! its really helping me alot to get through the research....thanks alot. — sonu, Dec 14 '11 at 01:16

Query for summation up to certain rows in a table

2 Answers2

Linked