SQLTeam.com | Weblogs | Forums

Subquery Sum with a Group by

sql2000

#1

Hello,

I'm having problem with a SUm, because I need a Sum after a Group by.

This is the query:

SELECT isnull (sum(overtime),0) as overtime, Operatore, Descrizione, Datalog, Ip, IpPc, NomePc from lst_activitiLog l inner join lst_activitiMachine m on l.IpPc=m.Ip where m.Operatore = '1002408' and overtime is not null and convert(varchar,l.datalog, 112) = '20151002' group by Operatore, Descrizione, Datalog, Ip, IpPc, NomePc order by Operatore,Datalog 

the result is:

I need a subquery which summarize the first column 113 +31 = 144.. I'm using MS SQL 2000 and I can't use Over, so I need another way to do this sum. Help me!!!


#2

The following query should work, although I'm not sure in which table the field "ip" belongs. I'm guessing the lst_activitylog table. (the other fields, I found the table names from your previous post).

select l2.overtime
      ,m.operatore
      ,l.descrizione
      ,l.datalog
      ,l.ip
      ,l.ippc
      ,l.nomepc
  from lst_activitilog as l
       inner join lst_activitimachine as m
               on m.ip=l.ippc
       inner join (select m.operatore
                         ,sum(isnull(l.overtime,0)) as overtime
                     from lst_activitilog as l
                          inner join lst_activitimachine as m
                                  on m.ip=l.ippc
                    where m.operatore = '1002408'
                      and m.overtime is not null
                      and l.datalog>=cast('20151002' as datetime)
                      and l.datalog<cast('20151003' as datetime)
                    group by m.operatore
                  ) as l2
               on l2.operatore=m.operatore
 where m.operatore = '1002408'
   and m.overtime is not null
   and l.datalog>=cast('20151002' as datetime)
   and l.datalog<cast('20151003' as datetime)
 order by m.operatore
         ,l.datalog
;

#3

Umh. It says to me:

Invalid column name 'overtime'. IP I changed the suffix and is ok.


#4

Almost done. There is a little problem, the query give me all the inactivties. As seem as don't take the "l.datalog>=cast('20151002' as datetime)"

select t.overtime
,m.operatore
,l.descrizione

   , left (convert(varchar,l.datalog, 112),10) as data
  ,m.ip
  ,l.ippc
  ,l.nomepc

from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
inner join (select m.operatore
,sum(isnull(l.overtime,0)) as overtime
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
where m.operatore = '1002408'
and overtime is not null

        and convert(varchar,l.datalog, 112) = '20151002'
		
                group by m.operatore
              )  as t
           on t.operatore=m.operatore

this work

The result this:

Instead I need just one day


#5

Ok. I did it.

select  t.overtime 
  ,m.operatore
  ,l.descrizione
  ,l.datalog
  ,left (convert(varchar,l.datalog, 112),10) as data
  ,m.ip
  ,l.ippc
  ,l.nomepc

from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
inner join (select m.operatore
,sum(isnull(l.overtime,0)) as overtime
from lst_activitilog as l
inner join lst_activitimachine as m
on m.ip=l.ippc
where m.operatore = '1002408'
and overtime is not null

        and convert(varchar,l.datalog, 112) = '20151002'
		
                group by m.operatore
              )  as t
           on t.operatore=m.operatore

where convert(varchar,l.datalog, 112) = '20151002'
and l.descrizione LIke '%Ripresa%'

Thank you so much! Probably the query it could be lighter, but the important thing is that it works now.


#6

Ok! The last problem. I have some case which the "l.descrizione LIke '%Ripresa%' is before the "Avvio", in this case I want only the "l.descrizione LIke '%Ripresa%' is between Avvio and Stop, if is before it mustn't do the sum, if the Ripresa is after is ok, if is before, don't work.


#7

I can carry with me the ID and do a compare, I think is the easyest way.

Last query:

select l.id,
	 
   t.overtime 
  ,m.operatore
  ,l.descrizione
  ,l.datalog
  ,left (convert(varchar,l.datalog, 112),10) as data
  ,right(convert(varchar,l.datalog, 108),10) as datatt,
  case
    
	WHEN Descrizione LIKE 'Avvio%' THEN 1 
	WHEN Descrizione LIKE 'ripresa%' THEN 3	
	WHEN Descrizione LIKE 'Stop%' THEN 4	
	END as test
   ,m.ip
  ,l.ippc
  ,l.nomepc
	 from lst_activitilog as l
   inner join lst_activitimachine as m
    on m.ip=l.ippc
   inner join (select 
	m.operatore
    ,sum(isnull(l.overtime,0)) as overtime 
    from lst_activitilog as l
    inner join lst_activitimachine as m
    on m.ip=l.ippc
    where m.operatore = '1002408'
	 
	and 
	CASE 
	WHEN Descrizione LIKE 'Avvio%' THEN 1 
	WHEN Descrizione LIKE 'ripresa%' THEN 3	
	WHEN Descrizione LIKE 'Stop%' THEN 4	
	END between 1 and 4
	and overtime is not null
    and convert(varchar,l.datalog, 112) = '20150930'
    group by m.operatore  
    )  as t
    on t.operatore=m.operatore
	where convert(varchar,l.datalog, 112) = '20150930'
	
	order by l.datalog

Result:

If I can check ID 7184 (the first START) is greater than the other IDS, I win the game!!!


#8

Filtering on datetime the way I showed, should work if your field type is datetime (looks like it is from your sample data). Also, the way I showed is much faster on tables with many rows.
To test, you could try running this:

select *
  from lst_activitilog
 where datalog>=cast('20150930' as datetime)
   and datalog<cast('20150930' as datetime)
;

or this:

select *
  from lst_activitilog
 where datalog>=cast('20150930 00:00:00.000' as datetime)
   and datalog<cast('20151001 00:00:00.000' as datetime)
;

or this:

select *
  from lst_activitilog
 where datalog>=convert(datetime,'20150930')
   and datalog<convert(datetime,'20151001')
;

or this:

select *
  from lst_activitilog
 where datalog>=convert(datetime,'20150930 00:00:00.000')
   and datalog<convert(datetime,'20151001 00:00:00.000')
;

It should show data for 30-09-2015 only. Can you confirm this? If not, please show table definition (create statement).

Now, the last output you show - what should the correct output look like?