OK - do my best to explain properly.

I need to pull a list of 3 correlated ID #s from a table called 'sales_rep'

sales_region_number, sales_office_number, sales_rep_number (example data is 1500,1000,1000 - which is the full rep number when concatenated)

and when I pull that list (all sales_reps essentially) I need to get a COUNT of the number of sales (new orders) from 'btn_master' table. Which only has a primary key of customer_number, of which the sales_rep table doesn't have, it is located in customer_master. This is the query I use to get a list of sales_reps and the number of new orders... and it is verified to work...The date range is going to be a variable in an asp page but I can handle that... This list is a list of all reps and their respective offices and regions. But it only pulls the reps who have a new order in btn_master... I need a list of all reps and the count of new orders for those that have some, so some reps in the list would have a 0 as new order amounts in the count column....

Please dear god help me - I have only done sql for a few weeks and am at my wits end...

Below, the sales_rep table is related to the customer master table which is how I relate the rep number to btn_master and get sales amount... each new record (row) in btn_master is a new order. So I am just counting the system_btn field

The results so far can be viewed at http://lan.accxx.com/sqlquery.asp and choose the BTNS per sales office report from the drop down. But in order to compare sales to previous months - I need to include the reps with 0 new orders (or 0 rows in the date range from the btn_master table)...

HELP ME OBI ONE, YOUR MY ONLY HOPE....
Nick

CODE BELOW^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^

SELECT sales_representative.sales_region_number,
    sales_representative.sales_office_number,
    sales_representative.sales_rep_number,
    COUNT(btn_master.system_btn) AS 'new_orders'

FROM sales_representative LEFT JOIN customer_master
    ON (sales_representative.sales_rep_number=customer_ma ster.sales_number_1) AND
        (sales_representative.sales_office_number=customer _master.sales_office) AND
            (sales_representative.sales_region_number=customer _master.sales_region_number)
    LEFT JOIN btn_master
    ON (customer_master.customer_number=btn_master.custom er_number)

WHERE (customer_master.effective_date BETWEEN '08/01/2003' AND '08/11/2003') AND
    (btn_master.system_btn <> 0)

GROUP BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number

ORDER BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number ;

UPDATE

I have gotten this far... altered code is below , but this now returns a count of the total and looks like this

sales_region | sales_office | sales_rep_number | New_orders
1500 1000 1000 470
1500 1000 1001 470
1500 1000 1002 470

and so on... the list of region, office, and rep numbers are correct (2,265 total) and the number of new orders are correct (470 total) but how do I get it to seperate so 470 is divided up among their respective sales reps... the table should result with this:

sales_region | sales_office | sales_rep_number | New_orders
1500 1000 1000 100
1500 1000 1001 200
1500 1000 1002 170

for example... the total orders are 470 but appropriately divided per sales rep...

UPDATED CODE^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

SELECT sales_representative.sales_region_number,
    sales_representative.sales_office_number,
    sales_representative.sales_rep_number,
    (
    SELECT COUNT(*)
    FROM customer_master RIGHT JOIN btn_master
        ON (customer_master.customer_number=btn_master.custom er_number)

    WHERE (customer_master.effective_date BETWEEN '08/01/2003' AND '08/11/2003') AND
            (btn_master.system_btn <> 0)

    )

FROM sales_representative

GROUP BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number
ORDER BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number ;

If you want to count the rows where there are no orders, why are you selecting only those rows which have a non zero btn_master.system_btn value? Those rows which have no corresponding btn_master entries will have their values set to NULL as a result of the OUTER JOINs - COUNTing them will give you 0.

Try something like:

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Just a quick question that isn't related to your specific problem, what is the purpose of having two 'GROUP BY' clauses that are exactly the same?

I was under the impression that using two 'GROUP BY' clauses in one SELECT statement would not work (unless it was a UNION query), however as Jeff did not correct the original statement I must be wrong.

Regards
Owain Williams

Look closer. One is a GROUP BY, the other is an ORDER BY.

Just because you GROUP BY something is no guarantee that the resultset will be in that order, so to get things in the in same order as the grouping, you need both clauses.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

This is the code I ended up using and it produced the perfect report with historical comparison for 90 days or however long I want by adding additional sub queries and changing the date range.
Today I am putting it in an asp page and allowing the user to pass the original date range and the history will just be -30,-60,-90, etc.

In the original queries I just wasn't relating the sub - query(s) to the outer query because I didn't think you could relate fields from seperate(nested) selects.

thanks for the help.
Nick


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^CODE^^^^^^^^^^^^^^^
SELECT
sales_representative.sales_region_number,
sales_representative.sales_office_number,
sales_representative.sales_rep_number,
sales_representative.last_name,
    (SELECT sales_region.sales_region_name
        FROM sales_region
        WHERE
        (sales_region.sales_region_number=sales_representa tive.sales_region_number)
        ) as 'rep_region_name',
    (SELECT sales_office.sales_office_name
        FROM sales_office
        WHERE
        (sales_representative.sales_office_number=sales_of fice.sales_office_number) AND
        (sales_representative.sales_region_number=sales_of fice.sales_region_number)
        ) as 'rep_office_name',


    (SELECT COUNT(*)
        FROM customer_master RIGHT JOIN btn_master
        ON (customer_master.customer_number=btn_master.custom er_number)

        WHERE
        (customer_master.sales_region_number=sales_represe ntative.sales_region_number) AND
        (customer_master.sales_office=sales_representative .sales_office_number) AND
        (customer_master.sales_number_1=sales_representati ve.sales_rep_number) AND
        (customer_master.effective_date BETWEEN '08/01/2003' AND '08/11/2003') AND
        (btn_master.system_btn <> 0) AND
        (customer_master.billing_cycle <> 't')
        ) as 'current_new',

    (SELECT COUNT(*)
        FROM customer_master RIGHT JOIN btn_master
        ON (customer_master.customer_number=btn_master.custom er_number)

        WHERE
        (customer_master.sales_region_number=sales_represe ntative.sales_region_number) AND
        (customer_master.sales_office=sales_representative .sales_office_number) AND
        (customer_master.sales_number_1=sales_representati ve.sales_rep_number) AND
        (customer_master.effective_date BETWEEN '07/01/2003' AND '07/31/2003') AND
        (btn_master.system_btn <> 0) AND
        (customer_master.billing_cycle <> 't')
        ) as '30Prior_new',

    (SELECT COUNT(*)
        FROM customer_master RIGHT JOIN btn_master
        ON (customer_master.customer_number=btn_master.custom er_number)

        WHERE
        (customer_master.sales_region_number=sales_represe ntative.sales_region_number) AND
        (customer_master.sales_office=sales_representative .sales_office_number) AND
        (customer_master.sales_number_1=sales_representati ve.sales_rep_number) AND
        (customer_master.effective_date BETWEEN '06/01/2003' AND '06/30/2003') AND
        (btn_master.system_btn <> 0) AND
        (customer_master.billing_cycle <> 't')
        ) as '60Prior_new'

FROM
sales_representative

GROUP BY
sales_representative.sales_region_number,
sales_representative.sales_office_number,
sales_representative.sales_rep_number,
sales_representative.last_name

ORDER BY
sales_representative.sales_region_number,
sales_representative.sales_office_number,
sales_representative.sales_rep_number,
sales_representative.last_name ;

I agree that you need correlated subqueries to calculate the counts you need, since the counts are based on different criteria. However, you do not need subqueries for 'rep_region_name' and 'rep_office_name'. A JOIN to the 'sales_representative' table would suffice for those.

Many times a correlated subquery and a JOIN end up with equivalent query execution plans, but not always. IMO, though, a JOIN is easier to read and understand what is going on. YMMV.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

 Oh yea.

Obviously I need to touch up on my observation skills as well as my SQL skills!

Regards
Owain Williams

Don't you hate it when that happens? :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com