Report query

bhuntsman · 2012-07-20 21:08:00

Pardon the noise, but SQL's not my strong suit, and I figured I'd farm this out here before posting to a more general SQL list. I'm trying to run a custom report in GLPI using the Reports plugin. The idea of the report is to provide a list of software installed on computers that belong to a specific location, even if the particular piece of software isn't listed as belonging to the location. Versions aren't important, but I'd like to have two columns returned: one with the ID of the software, and one with the count of number of computers with that software installed. Make sense:

softwares_id count(computers)
-------------------------------------------------

anyway, I'm using the following query to return the distinct software ID's for all the software installed on computers in a location... this is probably a terribly way to do it, but it works. The part I'm having trouble with is getting the count.

select distinct software from (SELECT `glpi_softwareversions`.`softwares_id` AS software,
`glpi_softwareversions`.`id` AS version,
`glpi_computers`.`id` AS computer,
`glpi_locations`.`completename` as location
FROM `glpi_softwareversions`
INNER JOIN `glpi_computers_softwareversions`
ON (`glpi_computers_softwareversions`.`softwareversions_id`
= `glpi_softwareversions`.`id`)
INNER JOIN (SELECT * FROM `glpi_computers` where `locations_id`=2)
`glpi_computers`
ON (`glpi_computers_softwareversions`.`computers_id` = `glpi_computers`.`id`)
LEFT JOIN `glpi_locations`
ON (`glpi_locations`.`id` = `glpi_computers`.`locations_id`)
order by `software`) a;

the derived table looks like this:

software version computer location
----------------------------------------------------------------------
1 2805 150 somewhere
1 2805 1866 somewhere
2 2 137 somewhere
2 2 150 somewhere
2 2 550 somewhere

so in this case, I'd want my query to return the following results:

software installedcount
------------------------------------------
1 2
2 3

Anyone know how to pull that off? Or a better way to write the overall query to get to the same results? (the key being searching for sofwtare based on the computer's location, not the software's)

Many, many thanks in advance!!

bhuntsman · 2012-07-20 21:54:34

PS, to make things interesting, I'll PayPal $10 to the first person with a working reply...

Thanks!

remi · 2012-07-21 07:48:22

SELECT `glpi_softwareversions`.`softwares_id` AS software, count(*) as installedcount
...
GROUP BY  `glpi_softwareversions`.`softwares_id`

Forum GLPI-Project

Announcement

#1 2012-07-20 21:08:00

Report query

#2 2012-07-20 21:54:34

Re: Report query

#3 2012-07-21 07:48:22

Re: Report query

Board footer