Office numbered totaled by Department

[tessymay]

I already have reports to break down the computers by our departments. Now I'm trying to apply that with getting a totaled number of what Office versions they have within each department. This is what I have so far. I have another report that totals the numbers correctly but this one just says 1 for each... Can you help me out? I need it to come out with 2010 Pro there is Xnumber, 2003 Pro there is Xnumber, etc.

Select Top 100 Percent dbo.tblSoftware.softwareName As Software,
dbo.tblSoftware.SoftwarePublisher As Publisher,
Count(dbo.tblSoftware.ComputerName) As Total, dbo.tblComputers.Computername
From dbo.tblSoftware Inner Join
dbo.tblComputers On dbo.tblSoftware.ComputerName =
dbo.tblComputers.Computername
Where (dbo.tblSoftware.softwareName Like 'Microsoft Office 2000 Professional'
And dbo.tblComputers.Computer Like 'it%') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Edition 2003')
Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional Plus 2007 ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Standard ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Professional 2010 ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office Standard Edition 2003 ') Or
(dbo.tblSoftware.softwareName = 'Microsoft Office XP Small Business ')
Group By dbo.tblSoftware.softwareName, dbo.tblSoftware.SoftwarePublisher,
dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique
Order By Count(dbo.tblSoftware.ComputerName)

[JasonG]

It seems I was mistaken, LS does store the Department attribute. The site I was working from doesn't set the Department on computer accounts, only Users.

SELECT
        softwareName AS [Software]
        ,department AS [Department]
        ,COUNT(*) AS [Installed]
    FROM tblSoftware
    LEFT JOIN tblCompCustom
        ON tblSoftware.Computername = tblCompCustom.Computername
    WHERE
        softwareName LIKE 'Microsoft Office%'
        AND softwareName NOT LIKE '%Communications Server%'
        AND softwareName NOT LIKE '%Live Meeting%'
        AND softwareName NOT LIKE '%Viewer%'
        AND softwareName NOT LIKE '%Communicator%'
        AND softwareName NOT LIKE '%Web Components%'
        AND softwareName NOT LIKE '%Resource Kit%'
    GROUP BY softwareName, Department
    ORDER BY SoftwareName, Department

[JasonG]

!!! Ignore this, correction below

As far as I can see, grouping by the AD Department attribute is not possible as LS does not store it anywhere for computers. It is stored for users, but that doesn't help us here. It is rather easy to do by OU. Consider the following:

SELECT
		softwareName AS [Software]
		,OU AS [OU]
		,COUNT(*) AS [Installed]
	FROM tblSoftware
	LEFT JOIN tblADComputers
		ON tblSoftware.Computername = tblADComputers.Computername
	WHERE
		softwareName LIKE 'Microsoft Office%'
		AND softwareName NOT LIKE '%Communications Server%'
		AND softwareName NOT LIKE '%Live Meeting%'
		AND softwareName NOT LIKE '%Viewer%'
		AND softwareName NOT LIKE '%Communicator%'
		AND softwareName NOT LIKE '%Web Components%'
		AND softwareName NOT LIKE '%Resource Kit%'
	GROUP BY softwareName, OU
	ORDER BY SoftwareName, OU

[Hemoco]

most likely a problem with the and/or

try this:

Select Top 100 Percent dbo.tblSoftware.softwareName As Software,
  dbo.tblSoftware.SoftwarePublisher As Publisher,
  Count(dbo.tblSoftware.ComputerName) As Total, dbo.tblComputers.Computername
From dbo.tblSoftware Inner Join
  dbo.tblComputers On dbo.tblSoftware.ComputerName =  
    dbo.tblComputers.Computername
Where ((dbo.tblSoftware.softwareName Like 'Microsoft Office 2000 Professional')
    Or
    (dbo.tblSoftware.softwareName =
      'Microsoft Office Professional Edition 2003') Or
    (dbo.tblSoftware.softwareName = 'Microsoft Office Professional Plus 2007 ')
    Or (dbo.tblSoftware.softwareName = 'Microsoft Office XP Professional ') Or
    (dbo.tblSoftware.softwareName = 'Microsoft Office XP Standard ') Or
    (dbo.tblSoftware.softwareName = 'Microsoft Office Professional 2010 ') Or
    (dbo.tblSoftware.softwareName = 'Microsoft Office Standard Edition 2003 ')
    Or (dbo.tblSoftware.softwareName = 'Microsoft Office XP Small Business '))
  And dbo.tblComputers.Computer Like 'it%'
Group By dbo.tblSoftware.softwareName, dbo.tblSoftware.SoftwarePublisher,
  dbo.tblComputers.Computername, dbo.tblComputers.ComputerUnique
Order By Count(dbo.tblSoftware.ComputerName)