Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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.
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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!
Reply With Quote
  #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
Reply With Quote
  #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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:29 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.