Solved

select top 1 count(*) returning count of more than one record?

Posted on 2008-10-13
6
2,011 Views
Last Modified: 2012-05-05
Ok I'm building a query to create a view in SQL server 2005 for work. One of the columns of the view is a simple 0/1 answer.

0 = the user in this record, has no records in TableB
1 = the user in this record has 1 or more records in TableB

So that column is simply a subquery (below)

The layout of TableB is as follows:
SHCDISTN
SHCRCD
SHCSTDT
SHCNBR
SCHCODE

there are no primary keys or relations to this table really. The SHCSTDT is a student number, so every medical 'code' has it's own record in the table with the corresponding student number. So student number "xyz" may have 3 records, no records, or 10000 record in TableB. Now given that, back to the subquery.

I figured I'd be tricky and simply do a count() for all records with that student number (xyz) but limit the result set to 1 (so the response is either 0 or 1 as desired).


The only issue is when the student number (xyz in this case) has 2 or more records in the table, instead of returning a "1", it returns the number of records they have, ignoring the "TOP 1" result set limit.

I come from a MySQL background so the MSSQL is still a little new to me. Am I missing a certain keyword or something? I've also tried to "group by SHCSTDT" but still get the same result. If needed I can clarify better.
////////////sub query

      (

        SELECT TOP 1

            COUNT(SHCSTDT) AS total

        FROM

            dbo.SHC

        WHERE

            ( SHCSTDT = dbo.SBR.SBRSTDT )

      ) AS [Medical Problems]

Open in new window

0
Comment
Question by:MMDeveloper
  • 3
  • 2
6 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 22701739
Hello MMDeveloper,

You need a group by:

        (
          SELECT TOP 1
              COUNT(SHCSTDT) AS total
          FROM
              dbo.SHC
          WHERE
              ( SHCSTDT = dbo.SBR.SBRSTDT )
          GROUP BY SHCSTDT
        ) AS [Medical Problems]

Regards,

TimCottee
0
 
LVL 11

Accepted Solution

by:
Otana earned 500 total points
ID: 22701753
The top 1 will return the first record of the resultset in which the count has already been done, so you get the full count.

Try this for subquery:

(SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.SHC WHERE SHCSTDT = dbo.SBR.SBRSTDT ) THEN 1 ELSE 0 END) AS [Medical Problems]
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22701759
     (
        SELECT TOP 1
            COUNT(1) AS Expr1
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
        GROUP BY
            SHCSTDT
      ) AS [Medical Problem]


I still get results higher than 1. I now get null, 0, 1, 2, 3, 4, 5, etc.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 15

Author Comment

by:MMDeveloper
ID: 22701796
Well I didn't 'fix' it but I just used a workaround for now.


      (
        SELECT
            CASE WHEN COUNT(*) >= 1 THEN '1'
                 ELSE '0'
            END AS Expr1
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
      ) AS [Medical Problem]
0
 
LVL 11

Expert Comment

by:Otana
ID: 22701812
Did you try my solution? It should be faster than yours.
0
 
LVL 15

Author Closing Comment

by:MMDeveloper
ID: 31505568
Sorry we must have been posting at the same time and I didn't see yours. Your solution worked just fine and I appreciate your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now