SELECT SUM not really adding up

Transact-SQL
1

Hi!

I have an SQL query where I want to choose in a table individual names from one column that occur more than once in another column. My query so far looks like this:

SELECT
PatientCode, BirthDate, COUNT() AS regionCount
FROM
dbo.Prescriptions
GROUP BY
PatientCode, BirthDate
HAVING
COUNT() > 1
SELECT
SUM (regionCount) AS Total
FROM
dbo.Prescriptions;

I assume (at least) there is an error in line 10:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'regionCount'.

In line 1 I try to create a temporary column name "regionCount" since if I don't, all the counted values will end up in a column "No column name" and such a name of a column seems not to be accepted.
What I really would like to do is to add all values from the "No column name" column:-)

Any ideas?

Thank you!

/Paul

2

SELECT
PatientCode, count(PatientCode) as regionCount,
BirthDate
FROM
dbo.Prescriptions
GROUP BY
PatientCode,
BirthDate
HAVING
count(PatientCode)> 1

3

Thank you ahmed08!

Your query does make all numbers show up in the column "regionCount". However their values are not added together. Is there a way to sum up all values in "regionCount"?

Thanks for your time and effort.

/Paul

4

can you give this a try

select sum(regionCount) as totalcount
from(SELECT
PatientCode, count(PatientCode) as regionCount,
BirthDate
FROM
dbo.Prescriptions
GROUP BY
PatientCode,
BirthDate
HAVING
count(PatientCode)> 1)

5

For some reason it throws:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.

And when I click the parenthesis it shows:

"Incorrect syntax near 'End Of File'. Expecting AS, ID, or QUOTED_ID."

/Paul

6

select sum(regionCount)
from(SELECT
PatientCode, count(PatientCode) as regionCount,
BirthDate
FROM
dbo.Prescriptions
GROUP BY
PatientCode,
BirthDate
HAVING
count(PatientCode)> 1) a

7

I fixed it. Just added "AS totaltcount"

Thank you ahmed08:-)

1 Like