Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    UK
    Posts
    9

    Unanswered: 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

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could combine them with UNION ALL, but it would be klugey

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    UK
    Posts
    9
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Group totals and details in one database query

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    706
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •