SQL Query - Newbie help with summaries

[UnderCoverGuy]

Hello. I am trying to produce a SQL report query that determines the type (model) of system a user has and based off of that information, CASE selects to determine the amount of and type of memory needed to upgrade to 1GB. What I need to do is find a way to tally the information at the end of the report to produce a summary. I was thinking about trying a GROUP BY and SUM on that, but haven't been able to get it to work properly. Any thoughts on how I can approach this?

Thanks in advance for any help,
UCG

------------------------------------------------------------------------------------------
select distinct
v_GS_Computer_system.Name0 AS "PC Name"
, v_GS_Computer_system.UserName0 AS "User last logged on"
, v_GS_Operating_System.caption0 AS "OS"
, v_gs_Operating_system.csdversion0 AS "SP Level"
, v_GS_Computer_system.manufacturer0 AS "Make"
, v_GS_Computer_system.model0 AS "Model"
, v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"

, case v_GS_Computer_system.model0
when 'Virtual Machine' then 'Virtual'
when 'VMware Virtual Platform' then 'Virtual'
when 'Evo D310v' then '1'
when 'Evo D510 CMT' then '1'
when 'Evo D510 SFF' then '1'
when 'Evo W4000' then 'Unknown - Varies'
when 'Workstation W6000' then 'Unknown - Varies'
when 'HP dx5150 SFF(PZ585UA)' then '1'
when 'HP d220 MT (DS941A)' then 'Unknown - Varies'
else 'Unknown'
end as 'RAM quantity to achive 1GB'

, case v_GS_Computer_system.model0
when 'Virtual Machine' then 'Virtual'
when 'VMware Virtual Platform' then 'Virtual'
when 'Evo D310v' then '1GB PC2700'
when 'Evo D510 CMT' then '1GB PC2700'
when 'Evo D510 SFF' then '1GB PC2700'
when 'Evo W4000' then 'Need to examine system'
when 'Workstation W6000' then 'Unknown - believe 2 GB'
when 'HP dx5150 SFF(PZ585UA)' then '1GB PC3200'
when 'HP d220 MT (DS941A)' then 'Need to examine system'
else 'Unknown'
end as 'Ram Type'

, v_gs_processor.maxclockspeed0 AS "CPU Speed"

, cast(v_gs_video_controller.CurrentHorizontalResolu tion0 as Varchar) + ' x
' + cast(v_gs_video_controller.CurrentVerticalResoluti on0 as varchar) AS
"Video Resolution"

from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_gs_video_controller,
v_GS_Operating_system

WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_video_controller.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 NOT like '%2000 Server%'
and v_GS_Operating_system.caption0 NOT like '%Server 2003%'
and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
and v_GS_x86_PC_memory.totalphysicalmemory0 < '1024768'

Order by
v_gs_computer_system.name0
------------------------------------------------------------------------------------------

[iburyak]

I don't see what you want to summarize?
Show me end result that you are trying to get or show me your query that produces an error so I could help you if possible.

[UnderCoverGuy]

Thanks. There are a number of systems in the database (it is an SMS database) and the query itself is much larger, but I trimmed it down to just a few example systems to post it here. I am trying to summarize the totals for the amount of RAM needed by RAM type, based on system model and I haven't been able to make it work.

When the query goes out, it determines the type of system (v_GS_Computer_system.model0). Based on the data, the query then will tell you the type of RAM that needs to be installed to achieve 1GB ('Ram Type'). There are a few thousand records and the query works fine itself to produce a detailed report showing each system and what is needed, but what I also need to figure out is how to total the ('RAM quantity to achive 1GB') field - which is not always going to be numeric - based on ('Ram Type'). I guess, in other words, I need the query to find all matching ('Ram Type') fields and provide a summary of the adjacent ('RAM quantity to achive 1GB'). I had thought of using a COUNT to simply count how many of each ('Ram Type') exists, but sometimes the ('RAM quantity to achive 1GB') may be more then 1. Also, I tried the GROUP BY and wanted to SUM the group, but could not get that to work properly either, as I cannot figure out the column name, The error that I receive with the GROUP BY is that the column name is incorrect when I try to use (v_GS_Computer_system.model0) or ['RAM quantity to achive 1GB'] or ['Ram Type'].

The problem for me has really come from the fact that both ('Ram Type') and ('RAM quantity to achive 1GB') come from the same (v_GS_Computer_system.model0) field. I hope that I explained that properly?

It has been a while since I have done SQL queries and I am definately rusty on this subject.

Thanks in advance for any assistance,
UCG