Help with report showing Count

[Ismail]

Hello, I have this report:

SELECT DISTINCT 
  dbo.tblSoftware.softwareName AS Software,
  dbo.tblSoftware.ComputerName,
  dbo.tblSoftware.Installdate,
  dbo.tblSoftware.Lastchanged,
  dbo.tblSoftware.softwareVersion,
  tblComputers.LastknownIP,
  tblCompCustom.Department
FROM
  dbo.tblSoftware
  INNER JOIN tblComputers ON (dbo.tblSoftware.ComputerName = tblComputers.Computername)
  LEFT OUTER JOIN tblCompCustom ON (dbo.tblSoftware.ComputerName = tblCompCustom.Computername)
WHERE
  dbo.tblSoftware.softwareName = 'Symantec Antivirus'

I would like to show how many each department I have Installed, something like the predefined "All installed Software"

Instead of the total of how many per software I would like how many per department.

This would be a secondary report to the original report attached where it would say:


HR DEPT: 20
IT DEPT 40
FINANCE: 50

etc...

It would be great if somehow it would show how many computers that dont have the symantec AV installed per dept as well.


Regards and many thanks.

[Ismail]

Very nice indeed, thank you

[Hemoco]

strange, works for me, can you try this one:

SELECT DISTINCT 
                      TOP 100 PERCENT dbo.tblCompCustom.Department, dbo.tblSoftware.softwareName, dbo.tblSoftware.softwareVersion, 
                      COUNT(dbo.tblSoftware.ComputerName) AS Total
FROM         dbo.tblSoftware FULL OUTER JOIN
                      dbo.tblCompCustom ON dbo.tblSoftware.ComputerName = dbo.tblCompCustom.Computername
GROUP BY dbo.tblSoftware.softwareName, dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion
HAVING      (dbo.tblSoftware.softwareName = 'Symantec Antivirus')
ORDER BY dbo.tblCompCustom.Department

[Ismail]

It gives me an error report stating; Column 'dbo.tblSoftwareName' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause

[Hemoco]

thy this:

SELECT DISTINCT TOP 100 PERCENT dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion, COUNT(dbo.tblSoftware.ComputerName) AS ComputerName
FROM dbo.tblSoftware FULL OUTER JOIN dbo.tblCompCustom ON dbo.tblSoftware.ComputerName = dbo.tblCompCustom.Computername GROUP BY dbo.tblSoftware.softwareName, dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion HAVING  (dbo.tblSoftware.softwareName = 'Symantec Antivirus')
ORDER BY dbo.tblCompCustom.Department

[Ismail]

Thanks for your input but sadly the report you have given me results in invalid syntax

[EPiServer]

Try this one


SELECT DISTINCT
TOP (100) PERCENT dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion, COUNT(dbo.tblSoftware.ComputerName)
AS ComputerName
FROM dbo.tblSoftware FULL OUTER JOIN
dbo.tblCompCustom ON dbo.tblSoftware.ComputerName = dbo.tblCompCustom.Computername
GROUP BY dbo.tblSoftware.softwareName, dbo.tblCompCustom.Department, dbo.tblSoftware.softwareVersion
HAVING (dbo.tblSoftware.softwareName = N'Symantec Antivirus')
ORDER BY dbo.tblCompCustom.Department