Need SQL Syntax help - Is this possible?

I am looking for a way to count a row only if it is unique.  I have done this in the past when I copy the data out and place it in Excel I look at the row above and if it is the same then I give the row a 0 otherwise it is unique and it gets a 1
=IF(A3=A4, 0,1)

Open in new window


this is what I have in Excel sorry the formatting is not that great but this is the basic idea of what I need for the output.  I will place the above formula in B3.  The first row shows that the patient has two rows for the same VISIT NUMBER.  The ACCOUNT NUMBER is the Patient Number so when that is the same it is the same patient.  So for this count we have a patient with one visit.  further down we will see we have 1 patient with 2 separate visits so I would want a 1 in each row.
                 A                       B                                          C                                        D
1                        
2      VISIT NUMBER      VISIT COUNT                  ACCOUNT NUMBER         PATIENT COUNT
3      1587608488               0                                          505298582                       0
4      1587608488               1                                          505298582                       1
5      1080198294               1                                          505365381                       1
6      1071559875              1                                          505382923                       0
7      1072592173              1                                          505382923                       1
8      3080034303              1                                          505429161                       1

I thought I might do a subquery but that just gave me a 1 all the way down and I see why.  I was telling it to give me the count for each visit.  Well a patient will only have 1 Visit per visit so that is right but not what I was looking for,  Even if I got the count right I would only want the total on the last row or even just on one row for that account.  In the above example I have 4 patients and 5 visits.  Does this make sense?

Thanks,
Rodger
LVL 4
RodgerSystems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
How does SQL applies here? Your example is a mere spreadsheet..

In SQL it is normally

SELECT COUNT(*) FROM tableName;

Open in new window


Cause normalization requires candidate keys. Thus a single attribute or a combination of attributes, which mark the row as unique (aka identify that row).
0
Dale FyeOwner, Developing Solutions LLCCommented:
or something like:

SELECT Count(*)
FROM (
SELECT VisitNumber, Count(*)
FROM yourTable
GROUP BY VisitNumber
HAVING Count(*) = 1
)
0
RodgerSystems AnalystAuthor Commented:
Sorry looks like I did not finish my thought.  I am looking to create a query for what hat I am doing in the spreadsheet in a SQL query.  

I am looking to add a column in the SQL query that has a value of 0 for the row when the visit number is equal to the next row if not than then value should be 1 like the spreadsheet above.

I still need to know how to evaluate the current row to the next row in the syntax.  Or the current row to the previous row.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCCommented:
So, you are doing this in Excel, not Access or SQL Server?
0
RodgerSystems AnalystAuthor Commented:
I have the query in SQL Server and then I move it into Excel and add the formula to check each row.  If I could I would like to do all of this in SQL.  currently I am doing this, but if someone comes in after me to do this I would like it so they can just run the query and get what we need for the report.

If What I want to do is better suited to just do in Excel then that is the answer.
0
ste5anSenior DeveloperCommented:
I am looking for a way to count a row only if it is unique.
This does not require to look at previous rows. This spreadsheet thinking. SQL is set-based. It uses relational theory.

When it is really SQL help you need, then post a concise and complete example. Post table DDL and sample data INSERT statements as one runnable T-SQL script.

And maybe you should rephrase your question.
0
RodgerSystems AnalystAuthor Commented:
This is a concept that I am asking about.    I am showing you an example of the end result and asking how I can create a SQL query to get the same end result.  

I am asking for help about a concept.  The question is phrased as such.  We are all here to learn from each other and not to scold each other because we do not approve the way a question was posted.  The example is complete and concise.
0
ste5anSenior DeveloperCommented:
Ok, then you need to start to create your model. Use either the ER or OR modelling. Then derive your data model from it. Normalize it. Then the query is a

SELECT COUNT(*) FROM yourTable;

Open in new window


p.s. hmm, I don't see source data in your question, only the desired output. How can this be considered complete?
0
Dale FyeOwner, Developing Solutions LLCCommented:
Roger,

Ste5an's point is that SQL Server is set based, not row based, thus, there is no "previous row".  Normally, in a database, you would have a date/time field or some other unique combination of fields which would allow you to sort the data, but the concept of next or previous row is not "database" speak.

in the sample data you provided, you have consecutive rows in your spreadsheet with the same VisitNumber or AccountNumber, but it's hard to tell whether those columns (A/B) and (C/D) actually relate to each other or whether they come from separate queries of your SQL data.

If we sort by one of those columns (I assume there is some WHERE clause in your actual query) you can get the sort order you show in your spreadsheet.  However, the data you provided above (copied below) does not exactly synch with your question.  You indicate you only want to count the row if it is unique, but in the data below, you count row 4 even though that row is not unique (row 3 is a match)

2      VISIT NUMBER      VISIT COUNT                  ACCOUNT NUMBER         PATIENT COUNT
3      1587608488               0                                          505298582                       0
4      1587608488               1                                          505298582                       1
5      1080198294               1                                          505365381                       1

What it looks like is that you only want to count each combination of VisitNumber or AccountNumber once.  In which case, why not eliminate the duplicates all together

2      VISIT NUMBER      VISIT COUNT                  ACCOUNT NUMBER         PATIENT COUNT
3      1587608488               1                                          505298582                       1
4      1080198294               1                                          505365381                       1

with a query like:

SELECT VisitNumber, AccountNumber, Count(*) as Lines, 1 as VisitCount, 1 as PatientCount
FROM yourTable
GROUP by VisitNumber, AccountNumber
WHERE VisitDate = '2018-03-30'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RodgerSystems AnalystAuthor Commented:
There is more data, but I did not think it was relevant  as I just wanted to know how or if  I could get the count on  the row in the data I had shown.  This is a payment query.  There are duplicate rows it has the date the payment was made, the type of payment and the amount of the payment.  I just wanted to see if there was a quick way to do what I was doing in Excel in SQL.  The data that I have is used to create a summary so I need all the rows just how I have it.   i did not think you needed to see all the payment information as it had nothing to do with the question.  To me i was just asking for a way to count the data.  I know SQL just pulls each row back.  I would really need to do more do this and it might be batter done outside of this query.  Again I was just thinking about it and if it could be done.  Next time I will include all the data as I know what I had you did not know what I had.

I get what you are both are saying.  Thank you Dale.  It just seems sometime people on here like to make others feel beneath them and that they are wrong in their asking of a question instead of trying to help.  I have been working with database for over 20 years.  I do understand what you are saying, but I was just asking for a concept of could this be done.  That is all.  

Again thanks Dale.
0
RodgerSystems AnalystAuthor Commented:
Thank you Dale
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.