Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 2nd, 2004, 12:12 PM
Registered User
 
Join Date: Dec 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT COUNT as a ROW??

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.


 
Old December 2nd, 2004, 01:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

 
Old December 3rd, 2004, 08:12 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
 
Old December 8th, 2004, 05:31 AM
Registered User
 
Join Date: Dec 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLDataSource row count areid ASP.NET 2.0 Basics 3 June 5th, 2010 06:02 AM
Row Count in each table ayazhoda Access VBA 0 April 15th, 2008 04:31 PM
Select row in GridView WITHOUT using Select button rao965 ASP.NET 2.0 Professional 1 February 15th, 2008 10:44 AM
Row count Discrepancy bph SQL Server 2000 9 January 26th, 2006 02:04 PM
Row Count Walden SQL Server 2000 6 June 9th, 2003 12:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.