SQL Query Help *UPDATE: Need a more complex query

[Hoober]

I have two tables. One table has computer names with a divisional prefix as the first 3 characters. The 2nd table contains the division names in one column and the prefixes in the other as they correspond to the divison name. I need a report that will generate a count of the total computers within a division:

Division name| Count of computers

So, what I've got is:

select divisiontable.name as 'Division Name', count(computertable.name) as 'Number of computers'
from divisontable, computertable
where computertable.name contains (select * from divisiontable.abbreviation)
order by divisiontable.name

Do I need to pass a variable to this thing? Anybody have any ideas? The return I get from this is blank.

 

[WannaFly]

rough draft, I dont have SQL accessible from here to test/try, should go something like this

select count(computertable.*), divisiontable.name from computertable,divisiontable where divisitiontable.[codefield]=computertable.[3 letter field] group by computertable.[3 letter field]

edit: fixed, had group by in front of where. This should do what you mention in th epost below, hoober.

 

[Hoober]

Thanks for the reply, Wannafly.

I need a list of all of the divisions and a count of the computers within that specific division. Sorry I wasn't clear. So, not just the count of computers in one division, but a list of all the divisions with a corresponding count.

 

[Armitage]

SELECT divisiontable.name AS 'Division Name', count(*) AS 'Number of computers'
FROM divisontable, computertable
WHERE left(computertable.name, 3)= divisiontable.abbreviation GROUP BY divisiontable.name
ORDER BY divisiontable.name

 

[Hoober]

Thanks Armitage, but I get another blank query answer... any other ideas?

 

[awal]

Can you post some sample data from the 2 tables? Armitage post looks like it should return the expected results.

 

[Hoober]

Originally posted by: adrianwalters
Can you post some sample data from the 2 tables? Armitage post looks like it should return the expected results.

Sure. In the Division table there are two columns:
Name Abbreviation
Admissions AD-
Business BU-
Human Resources HR-

In the computertable table you have a number of columns, but I figured it'd be easiest to do a count on the name

Name
AD-BRuin
AD-Boxer
AD-Botox
BU-Bourne
BU-Cathy
BU-Teller
HR-Mudd
HR-Puffs
HR-Duck

The computertable table also contains information about the computer's OS.

 

[awal]

Try this...

SELECT divisiontable.name AS 'Division Name', count(*) AS 'Number of computers'
FROM divisontable, computertable
WHERE left(computertable.name, 3)= RTRIM(divisiontable.abbreviation) GROUP BY divisiontable.name
ORDER BY divisiontable.name

 

[Armitage]

Originally posted by: Hoober
Thanks Armitage, but I get another blank query answer... any other ideas?

Works for me

 

[Hoober]

Originally posted by: Armitage

Originally posted by: Hoober
Thanks Armitage, but I get another blank query answer... any other ideas?

Works for me

Armitage, you're a blessing. I went back to the divisions table trying to figure out what was going on. Last night I had added '%' to the end of each abbreviation trying different things to get it to work. So I removed that wildcard and now everything works!

Can you explain the left join in line 3 so I can better understand this? I didn't even think about doing a join.

Thank you!

 

[Armitage]

Originally posted by: Hoober

Originally posted by: Armitage

Originally posted by: Hoober
Thanks Armitage, but I get another blank query answer... any other ideas?

Works for me

Armitage, you're a blessing. I went back to the divisions table trying to figure out what was going on. Last night I had added '%' to the end of each abbreviation trying different things to get it to work. So I removed that wildcard and now everything works!

Can you explain the left join in line 3 so I can better understand this? I didn't even think about doing a join.

Thank you!

No problem!
That's not a join. left(str, n) is a function that returns the first n characters of a string. So I'm pulling out the first 3 characters, and comparing them to the abbreviation column.

You are doing a full join because you're using two tables. A left join would have the 'LEFT' in the from clause.

 

[Hoober]

So my boss would like to complicate this further...

Within the computertable table there is a comlumn called 'OS.' It has the computers broken up by 'Microsoft' and 'Mac' depending on whether they are Mac-based OS's or Microsoft OS's. I'd like to put two more columns in the result set with a breakdown by division of the Macs and PCs and then a total count of computers. So the result set would be:

Division Name PCs Macs Total Number of Computers

Can anybody think of a way to break that up in the select statement?

 

[Hoober]

Anybody have any ideas? Some sort of nested query perhaps?

 

[MrChad]

I would use subqueries:

SELECT DISTINCT divisiontable.name AS 'Division Name',
(select count(*) from computertable where left(computertable.name, 3) = divisiontable.abbreviation and OS = 'Microsoft') AS 'PCs',
(select count(*) from computertable where left(computertable.name, 3) = divisiontable.abbreviation and OS = 'Mac') AS 'Mac',
(select count(*) from computertable where left(computertable.name, 3) = divisiontable.abbreviation) AS 'Total Number of Computers'
FROM divisiontable
ORDER BY divisiontable.name

 

[Armitage]

Originally posted by: Hoober
So my boss would like to complicate this further...

Within the computertable table there is a comlumn called 'OS.' It has the computers broken up by 'Microsoft' and 'Mac' depending on whether they are Mac-based OS's or Microsoft OS's. I'd like to put two more columns in the result set with a breakdown by division of the Macs and PCs and then a total count of computers. So the result set would be:

Division Name PCs Macs Total Number of Computers

Can anybody think of a way to break that up in the select statement?

If you take the query we have above, add 'os' to the select clause, and 'os' to the group by clause, you can the data on two seperate lines. IAW, it would look like this:

Division Name OS Num Computers
Human Resources Mac 10
Human Resources PC 4
Admissions Mac 2
Admissions PC 6
....

And you'd have to add those up to get the total. At least with MySQL (no subqueries, etc) I don't know of anyway to get the result formatted the way you suggest from a single query. That's what the script wrapping the queries is for

 

[Hoober]

I got it with a lot of help from one of the Oracle DBAs. Thankfully we share a common language.

Anyway, MS SQL 2000 supports nested queries or inline views (if you're of the Oracle persuasion). Basically, within a from statement you can next a SELECT query that creates a temp table on the database with the information from the SELECT statement. You can then do all kinds of joins and things. So after two outer joins the data came together.

Thank you all so much for your help. I wouldn't of even gotten it started without it.