SELECT COUNT as a ROW??

t0ny · December 2nd, 2004, 12:12 PM

Hi there, here is my dilema

I have a table of departments, with department codes, ie

department1 ONE
department2 TWO

I also have a table of PC's that are named with department codes, ie

ONE40567
ONE33345
TWO45443

I want to list all the departmens and next to them display how many PC's are in their department.

I have two queries:

1: SELECT * FROM department ORDER BY departmentname;

2: SELECT COUNT(DISTINCE computerrecno) AS 'computercount' FROM computers WHERE computername like 'TWO%'

So my first query lists the departments and the second returns a count of PC's where their name starts with TWO.

So; Can i merge these queries so my output looks like...

department1 402
department2 326

where the numbers are the pc's within a department?

Thanks for any help.

stu9820 · December 2nd, 2004, 01:27 PM

To write a statement like this we need more information on your tables. Is there any Primay/Foreign key relationships, etc... ?

SELECT departmentname, COUNT(computerrecno)
FROM department d, computers c
WHERE d.id_field = c.id_field
AND c.computername like 'TWO%'
GROUP BY departmentname

joefawcett · December 3rd, 2004, 08:12 AM

Not nice without proper foreign key but the following might do:

Code:

SELECT D.DeptName, COUNT(C.ComputerRecNo) AS ComputerCount
FROM  departments D, computers C
WHERE (C.ComputerRecNo LIKE D.DeptName + '%')
GROUP BY D.DeptName
ORDER BY D.DeptName

--

Joe (Microsoft MVP - XML)

t0ny · December 8th, 2004, 05:31 AM

Hi guys, thanks a lot with a little re-jigging that worked great. As usual i have been assigned another development role where I was not there for the database design; so things are missing..

here is what worked...

SELECT d.departmentname, COUNT(c.computerrecno) AS computercount
FROM department d, computers c
WHERE (c.computername LIKE d.departmentcode + '%')
GROUP BY d.departmentname
ORDER BY d.departmentname

i agree a nice department code in computers would be nice, but this thing picks all its data up from a service run on client machines; so the computernames are just what some WMI call is returning and it is not always that acurate due to engineers who install the PC's..

Thanks again...