how do i get the sum of this?

here's my query

SELECT DISTINCT tbl_filedetails.vfilepathclient, 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)))

How do i modify this so i get the sum of all the iactualsize records this returns?
LVL 4
kenshawAsked:
Who is Participating?
 
dqmqCommented:
This gives the sum along with the detail, but in a separate column:


SELECT DISTINCT tbl_filedetails.vfilepathclient, tbl_filedetails.iactualsize,  sum.iactualsizesum as total
FROM         tbl_filedetails
 INNER JOIN   tbl_userdetails ON tbl_filedetails.vuserid = tbl_userdetails.vuserid
 INNER JOIN                      
(
SELECT dis.vfilepathclient, sum(dis.iactualsize) as iactualsizesum
(
SELECT  distinct f.vfilepathclient, f.iactualsize
FROM         tbl_filedetails as f INNER JOIN
                      tbl_userdetails as u ON f.vuserid = u.vuserid
WHERE     (u.vuname = 'april10test') AND (NOT (f.vbkpk IN
                          (SELECT     d.vbkpk
                            FROM          tbl_deletedfiledetails as d
                            WHERE      d.vuserid = u.vuserid)))
group by f.vfilepathclient
) as dis
) as sum
WHERE     (tbl_userdetails.vuname = 'april10test') AND (NOT (tbl_filedetails.vbkpk IN
                          (SELECT     vbkpk
                            FROM          tbl_deletedfiledetails
                            WHERE      vuserid = tbl_userdetails.vuserid)))
0
 
Patrick MatthewsCommented:
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
0
 
imran_fastCommented:
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
0
 
LowfatspreadCommented:
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  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.