Sort and Group

,
1

Ok, I have the following script
SELECT DISTINCTROW UserProfile12_5.Computername, UserProfile12_5.User, UserProfile12_5.Path, UserProfile12_5.FileName, UserProfile12_5.FileCount, Sum(UserProfile12_5.Size) AS [Sum Of Size]
FROM UserProfile12_5
GROUP BY UserProfile12_5.Computername, UserProfile12_5.User, UserProfile12_5.Path, UserProfile12_5.FileName, UserProfile12_5.FileCount;

What I would like to do, if possible is get 2 scripts, I would like to group by computername and then get a total for size

then I would like to know how I could group by computername, group by filename on the last 4 characters, and then size.

Is it possble?

2

This perhaps:

select computername
      ,sum(size) as [sum of size]
  from userprofile12_5
 group by computername
;

select computername
      ,case
          when len(filename)>=4
          then right(filename,4)
          else filename
       end as filename_last4chars
      ,sum(size) as [sum of size]
  from userprofile12_5
 group by computername
         ,case
             when len(filename)>=4
             then right(filename,4)
             else filename
          end
;
3

That did not work

I want the 2 tables

When computernames are the same I want it to combine the name and give me the Total size of data, so one line per computername with a total size

The Second table I want to be FileName last 4 characters if multiple filenames are the same combine them and then sum of size for each group of files by Computername

4

That error do you get?

Please provide sample data (as insert statements) and expected output (from the sample data you provide).

5

I'm using SQL Server syntax but I just realized this was more likely Access.

SELECT 
    Computername, 
    RIGHT(FileName, 4) AS FileExtension,
    SUM([Sum Of Size]) AS TotalSize
FROM (
    SELECT DISTINCT UserProfile12_5.Computername, UserProfile12_5.[User], UserProfile12_5.Path, UserProfile12_5.FileName, UserProfile12_5.FileCount, Sum(UserProfile12_5.Size) AS [Sum Of Size]
    FROM UserProfile12_5
    GROUP BY UserProfile12_5.Computername, UserProfile12_5.[User], UserProfile12_5.Path, UserProfile12_5.FileName, UserProfile12_5.FileCount
) AS derived
GROUP BY Computername, RIGHT(FileName, 4) WITH ROLLUP
HAVING GROUPING(RIGHT(FileName, 4)) = 0 OR GROUPING(Computername) = 1