how do i get the sum of this?

Hi kenshaw,

SELECT tbl_filedetails.vfilepathclient, SUM(tbl_filedetails.iactualsize) AS SumOfSize
FROM         tbl_filedetails INNER JOIN
                      tbl_userdetails ON tbl_filedetails.vuserid = tbl_userdetails.vuserid
WHERE     (tbl_userdetails.vuname = 'april10test') AND (NOT (tbl_filedetails.vbkpk IN
                          (SELECT     vbkpk
                            FROM          tbl_deletedfiledetails
                            WHERE      vuserid = tbl_userdetails.vuserid)))
GROUP BY tbl_filedetails.vfilepathclient

Regards,

Patrick

SELECT  tbl_filedetails.vfilepathclient, sum(distinct tbl_filedetails.iactualsize)
FROM         tbl_filedetails INNER JOIN
                      tbl_userdetails ON tbl_filedetails.vuserid = tbl_userdetails.vuserid
WHERE     (tbl_userdetails.vuname = 'april10test') AND (NOT (tbl_filedetails.vbkpk IN
                          (SELECT     vbkpk
                            FROM          tbl_deletedfiledetails
                            WHERE      vuserid = tbl_userdetails.vuserid)))
group by tbl_filedetails.vfilepathclient

if you have multiple filedetails for a user in a filepath....
how do you want the filedetails to be selected ?

do you have the last accessed date or something similar?

e.g.

select f.vfilepathclient,sum(f.iactualsize)
  from tbl_fieldetails as f
 inner join tbluserdetails as u
     on u.vuserid=f.vuserid
 where u.vuname='april10test'
     and not exists (select vbkpk
                              from tbl_deletedfiledetails as x
                             where x.vuserid=u.vuserid)
    and f.dateaccessed = (select max(y.dateaccessed)
                                        from tbl_fieldetails as y
                                       where y.vfilepathclient=f.vfilepathclient
                                           and y.vuserrid=u.vuserid
                                    )

note exists syntax can perform better than in syntax


hth