I know this has to be easy...

[madcaesar]

Ok I can't seem to figure out how to write this query.
I have two tables:

One is a Job Category List, the other List of Jobs.

Table One:
JobID
JobCategory

Table Two:
IndividualJobID
JobID (secondary key)
JobTitle
JobDescription

Now, I have a query that displays all the job categories. But how do I write a query, so that I can also show the number of jobs in that category?

So the output would look like
Hospital Jobs (21 jobs)
Plumbing Jobs (33 jobs)
etc.

Please help!

Thanks!

[stoplosingsales]

I think it is pretty easy. I'm pretty sure it should be something like this:

SELECT *, count(JobID)
FROM TableOne, TableTwo
WHERE TableOne.JobID = TableTwo.JobID
ORDER BY TableTwo.JobID

In the display:

#jobDescription# (#count(TableTwo.JobID)# jobs)

(Sorry if the display part is confusing - I'm a ColdFusion guy!)

Good Luck! I hope this is right - or close enough to get you there :-)

[Hoaobrook]

SELECT *, count(JobID)
FROM TableOne, TableTwo
WHERE TableOne.JobID = TableTwo.JobID
ORDER BY TableTwo.JobID

There are a couple problems with this, but the biggest being that categories with zero jobs won't show. Actually, it will failed for ambiguous names too.

SELECT t1.JobCategory, COUNT(t2.JobId)
FROM TableOne t1
LEFT OUTER JOIN TableTwo t2 ON t2.JobID = t1.JobID

The LEFT OUTER JOIN will ensure that you always get a TableOne record, even if there are no records to join to.

[madcaesar]

Hmm thanks for the help, however it doesn't seem to work right. When i do it, it just shows (1) job for all the categories.

Maybe it will help if I show you the whole 2 databases. I'm not matching them up with ID here it how it looks:

Table One: (Job Categories)
CategoryID
CategoryABBR (I use the ABBR field to pass in the search form.)
CategoryText (I want to display the text)
Status (This is either Active of Inactive, I don't want inactive categoriesto be shown)

Table Two: (Invidivudal Jobs)
JobID
CategoryABBR (I want to match them on this)
JobTitle
JobAddress
Status (This is either Active of Inactive, I don't want inactive jobs to be counted)

Thanks for any and all help in advance!!

[Hoaobrook]

1. I'll assume the table names are concatenated.

2. Always join tables by ID's, much faster. Yo might not notice a difference with 10 records, but what about 100? 1000? Then you'll see a difference. There are only a handful of situations where you wouldn't, and if this applies here, make sure you index the ABBR fields.

Getting to the query....


SELECT t1.JobCategory, COUNT(t2.JobId) as TotalJobs
FROM JobCategories t1
LEFT OUTER JOIN IndividualJobs t2 ON t2.CategoryABBR = t1.CategoryABBR AND t2.Status = 'Active'
WHERE t1.Status = 'Active'
GROUP BY t1.CategoryABBR


EDIT: I added the status conditions.

[madcaesar]

Hi Hoaobrook, thanks for your help! Well, it seems it works, I mean now I can get the abbreviation to show and total jobs in that category. But I can't output the actual category name.

In your first line you have SELECT t1.JobCategory, I have to replace that with SELECT t1.CategoryABBR and then in my output I can output #CategoryABBR # (#TotalJobs#)

But i want to output #CategoryText# (#TotalJobs#)

If I do SELECT t1.CategoryText I get this error:

Column 't1.CategoryText ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

That is really where I've been hung up for a long time now lol...Thanks again :)

[Hoaobrook]

Basically, (and this is my bad), you need to out the same field in your first item that you are grouping by.

SELECT t1.(FIELDNAME), COUNT(t2.JobId) as TotalJobs
FROM JobCategories t1
LEFT OUTER JOIN IndividualJobs t2 ON t2.CategoryABBR = t1.CategoryABBR AND t2.Status = 'Active'
WHERE t1.Status = 'Active'
GROUP BY t1.(FIELDNAME)

In the above, amke sure that fieldname is the same in both locations, which can be 'CategoryText'

A more expensive query, but easier to get seperate labels:


SELECT t1.CategoryText, t1.CategoryABBR,

(SELECT COUNT(*)
FROM IndividualJobs t2
WHERE t2.CategoryABBR = t1.CategoryABBR
AND t2.Status = 'Active') as TotalJobs

FROM JobCategories t1
WHERE t1.Status = 'Active'


Note on this query that the sub query can only have one field in the SELECT statement (in this case 'COUNT()').

[madcaesar]

Thank you Hoaobrook the second query worked beautifully! The first one was ok, but I couldn't output the CategoryABBR into the form. Since that's what I use for the actual search. I use CategoryText to display the full name in the form for the user.

The second query does EXACTY what i needed :D I wish there was a bit longer explanation as to how you figure this out. Is there some reading you would recommend? Because I spent a lot of time trying to get it work before asking for help but I was just getting nowhere :(

Anyway... Thanks again!

[Hoaobrook]

Glad I could help... I can try to explain it, but not a whole lot of suggested reading - this comes from doing it for years. Don't get discouraged...

The first query is grouping the record sets, and when you do that, you can only retrieve a record value from the field you are grouping on. The rest could be different values and therefore the SQL doesn't know WHICH of the different values you want. Thus, you have to run a function on any other field (such as COUNT, MAX, MIN, etc). The returned value is the result of the function from all of the rows.

The second query is going directly to the table that has the list of items we need, and then as a field value we are running a second query getting the total records count, and returning it as a field result of the first query.