COUNT ON SELECT DISTINCT stmt

savoym · August 27th, 2003, 03:34 PM

Can I not do a count on this SELECT statement? I am getting this error: INCORRECT SYNTAX NEAR what is in red bold below. Any help would be appreciated.

SELECT COUNT(DISTINCT tmp1.*, tmp3.rpt_surr_user_id, tmp3.rpt_access_date, tmp3.rpt_date_entered, tmp3.rpt_update_date),
        CASE
            WHEN tmp3.rpt_surr_user_id = tmp1.surrogate_hcin_user_id
            AND tmp3.rpt_access_date = tmp1.access_date THEN 1
            ELSE 0
        END
        AS incdt_rec_reported,
        CASE
            WHEN tmp2.surrogate_user IS NOT NULL THEN 1
            ELSE 0
        END
         AS surr_for_review

    FROM #TEMP1 tmp1
    LEFT OUTER JOIN #TEMP2 tmp2
    ON tmp1.surrogate_user = tmp2.surrogate_user
    LEFT OUTER JOIN #TEMP3 tmp3
    ON tmp1.surrogate_hcin_user_id = tmp3.rpt_surr_user_id
    AND tmp1.access_date = tmp3.rpt_access_date
    ORDER BY tmp1.Surrogate_User

Jeff Mason · August 27th, 2003, 03:47 PM

The DISTINCT keyword, when used with an aggregate function such as COUNT, must consist of a single column name only.

What are you trying to do?

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

savoym · August 27th, 2003, 03:55 PM

I am trying to get a COUNT of what is contained in this SELECT statement. This actually is the last piece of a long stored procedure which I did not want to put all in here.

Thanks for your time.

Jeff Mason · August 27th, 2003, 04:16 PM

The COUNT(*) function counts the number of rows in a query, so:

Code:

SELECT COUNT(*) FROM #TEMP1 tmp1
    LEFT OUTER JOIN #TEMP2 tmp2
    ...

will count the rows in your query.

Since COUNT counts rows, you don't need to specify any columns.

Now, the kicker is that if your query contains duplicate rows, these are not removed, so they will be counted.

If you want the count to remove any duplicates, the query must remove them. You can handle this by nesting two SELECTS, that is, you COUNT the number of rows in a derived table which is made up of your query:

Code:

SELECT COUNT(*) FROM
   (SELECT DISTINCT tmp1.*, tmp3.rpt_surr_user_id, tmp3.rpt_access_date,
tmp3.rpt_date_entered, tmp3.rpt_update_date, ... ) as yourquery

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

savoym · August 28th, 2003, 05:57 AM

Jeff,

Thanks again for your time and help. I will try these options today.

Quote:

quote:Originally posted by Jeff Mason
The COUNT(*) function counts the number of rows in a query, so:

Code:

SELECT COUNT(*) FROM #TEMP1 tmp1
    LEFT OUTER JOIN #TEMP2 tmp2
    ...

will count the rows in your query.

Since COUNT counts rows, you don't need to specify any columns.

Now, the kicker is that if your query contains duplicate rows, these are not removed, so they will be counted.

If you want the count to remove any duplicates, the query must remove them. You can handle this by nesting two SELECTS, that is, you COUNT the number of rows in a derived table which is made up of your query:

Code:

SELECT COUNT(*) FROM
   (SELECT DISTINCT tmp1.*, tmp3.rpt_surr_user_id, tmp3.rpt_access_date,
tmp3.rpt_date_entered, tmp3.rpt_update_date, ... ) as yourquery

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

savoym · August 28th, 2003, 06:24 AM

Jeff,

You were correct in that there are instances of duplicates therefore my reason for using DISTINCT. I tried the 2nd option you gave due to dups and I am getting the following error (ANY SUGGESTIONS?):

LINE 149 - INCORRECT SYNTAX NEAR "," (I've highlighted in RED).

Here is what I did to my code:

SELECT COUNT(*) FROM
    (SELECT DISTINCT tmp1.*, tmp3.rpt_surr_user_id, tmp3.rpt_access_date, tmp3.rpt_date_entered, tmp3.rpt_update_date,
        CASE
            WHEN tmp3.rpt_surr_user_id = tmp1.surrogate_hcin_user_id
            AND tmp3.rpt_access_date = tmp1.access_date THEN 1
            ELSE 0
        END
        AS incdt_rec_reported,
        CASE
            WHEN tmp2.surrogate_user IS NOT NULL THEN 1
            ELSE 0
        END
         AS surr_for_review

    FROM #TEMP1 tmp1
    LEFT OUTER JOIN #TEMP2 tmp2
    ON tmp1.surrogate_user = tmp2.surrogate_user
    LEFT OUTER JOIN #TEMP3 tmp3
    ON tmp1.surrogate_hcin_user_id = tmp3.rpt_surr_user_id
    AND tmp1.access_date = tmp3.rpt_access_date)

Thanks!

Jeff Mason · August 28th, 2003, 06:45 AM

When you use a derived table, you must give it a name. Look closely at the example I gave - you'll see a 'as yourquery' at the end of, er, your query...

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

savoym · August 28th, 2003, 07:58 AM

Sorry, Jeff. My bad.

That took care of my problem.

Again, thanks for your time and help.