Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 27th, 2003, 03:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default COUNT ON SELECT DISTINCT stmt

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
  #2 (permalink)  
Old August 27th, 2003, 03:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old August 27th, 2003, 03:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
  #4 (permalink)  
Old August 27th, 2003, 04:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #5 (permalink)  
Old August 28th, 2003, 05:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #6 (permalink)  
Old August 28th, 2003, 06:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

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!
  #7 (permalink)  
Old August 28th, 2003, 06:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #8 (permalink)  
Old August 28th, 2003, 07:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sorry, Jeff. My bad.

That took care of my problem.

Again, thanks for your time and help.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Distinct Jonas Access 2 August 14th, 2006 01:03 PM
count distinct values Chris Cash XSLT 3 June 8th, 2006 04:55 PM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM
count distinct nodes alexshiell XSLT 2 January 27th, 2005 11:19 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.