Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.


Reply With Quote
  #2 (permalink)  
Old December 2nd, 2004, 01:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,111
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

Reply With Quote
  #3 (permalink)  
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)
Reply With Quote
  #4 (permalink)  
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...

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.