Join, Count and Group By

Hello,

I have two tables containing the following info I need to use in a query...

Table 1
---

repair_no
contactor_code

Table 2
---

repair_no
area_code
log_date

I have a query to list all contractors starting with code 'SC', for a given area and between the dates shown.

SELECT
a.repair_no,
a.contractor_code,
b.area_code,
b.log_date
FROM
contractors a,
repairs b
where
a.repair_no = b.repair_no and
a.contractor_code like 'SC%' and
b.area_code like 'CH%' and
b.log_date >= date('01.02.2003') and b.log_date <= date('01.02.2004')
order by
a.contractor_code

How can I use this to obtain and list the same fields as above, but provide a count of (and possibly group by) all similar contractors?

I can get the count I need with the following. Do I need to run the two queries separately or in some way combine the two?

select
a.contractor_code,
count(a.contractor_code) as contr_count
from
contractors a,
repairs b
where
a.repair_no = b.repair_no and
a.contractor_code like 'SC%' and
b.area_code like 'CH%' and
b.log_date >= date('01.02.2003') and b.log_date <= date('01.02.2004')
group by
a.contractor_code

Thanksyou could combine them with UNION ALL, but it would be kluge (http://www.clueless.com/jargon3.0.0/kluge.html)y

i can whip up an example for you if you really need it

if you are returning the detail rows to an application program, you can simply calculate the counts while printing them, and you wouldn't need the second query at all

however, if you want the totals to precede the details in your listing, as in this example:

contractor SC001 has the following 3 repairs:
b0032 416 2004-02-01
b0077 905 2004-02-03
b0032 416 2004-02-05

contractor SC002 has the following 2 repairs:
b0050 905 2004-02-02
b0066 905 2004-02-04

then you might want to give the union a try, otherwise you will have to do two sets of loops in your code, one to count the rows per contractor, and the second to print them|||Thanks.

I've got something working using the two separate queries, which is acceptable.

However, if you do have an example of a union, and you don't mind, I would be grateful to see it. It will at least give me something to play around with.

Many thanks.|||Group totals and details in one database query (http://r937.com/grouptotals.htm)

that page is an unfinished article (i.e. you cannot find it in the archives, it was never published on my site)

the content (i.e. the sql and coldfusion logic) is fine, i just never figured out how to mark it up with colours that i'm happy with|||A query can obtain its output either from table(s) or from other queries: something that is called a view.

The exact means of doing this depend upon what tool you are using, but you can expect to find it with any fully SQL-compliant tool. (Microsoft Access, for example, doesn't support the concept of "views" but does allow you to include a query as well as a table in a query-designw window; thus, the same result, at least for our purposes here.)

In your example, I'd suggest that you use this approach simply because it's easy to visualize. You see, you've already got a query that does the first part: selecting the base records you want. It's a fairly complicated query and it might be a pervasive one: that is, "something you might wish to use in the same way in lots of different places." If you base subsequent inputs directly upon this query, you'll only have to change this query; not a whole slew of 'em.

Final note: when you combine queries in this way, the query optimizer will consider all of them at once to determine their combined effect, building the execution plan accordingly. It doesn't actually "run them one-at-a-time." So you don't [necessarily] pay a performance penalty in your quest for clarity.

And I prize clarity just about most-of-all.