CODEWITHSUNDEEP
asp.netsqlinttype-conversionvarchar
user3290048
user3290048

Reputation: 445

How to convert varchar to int in SQL

How do you convert varchar to int in SQL? My code is as such but I've tried casting and converting but they don't work. Declare/set doesn't work either.

select 
    Branch.BranchNo, 
    COUNT(Member.MemberID) as 'Number of Members' 
from 
    Branch
where 
    'Number of Members' > 2

Error:

Conversion failed when converting the varchar value 'Number of Members' to data type int.

Upvotes: 0

Views: 1337

Answers (4)

Jonathan Simas
Jonathan Simas

Reputation: 320

You cannot use grouping functions as count in "where" statements. Try to use HAVING instead:

select Branch.BranchNo, COUNT(Member.MemberID) as 'Number of Members' from Branch HAVING COUNT(Member.MemberID) > 2

Anyway, to convert VARCHAR to INT will depends of your DB vendor:

Oracle: TO_NUMBER(varcharValue)

SQL Server: CAST(varcharValue AS INT)

Postgres: CAST(varcharValue AS INTEGER)

Upvotes: 0

madmannrw
madmannrw

Reputation: 11

SELECT
    BRAND.BRANCHNO,
    COUNT{MEMEBER.MEMBERID) AS [NUMBER OF MEMBERS]
FROM 
    BRANCH
GROUP BY 
    BRAND.BRANCHNO
HAVING 
    COUNT(MEMBER.MEMBERID) > 2

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

Use:

select Branch.BranchNo, COUNT(Member.MemberID) as 'Number of Members' from Branch
......
HAVING COUNT(Member.MemberID) > 2

Sql runs in the following order: from where group by having select order by.

You can't use the column alias in any clause except the order by.

You need to use the count function again instead of the alias you gave. Also, you can't use an aggregate function in the where clause. Where runs before any aggregation happens in the group by clause. Even if you don't explicitly have a group by clause sql will consider the entire result set as a group.

If you want to filter on an aggregate like sum/count/max it must happen in the having clause.

Upvotes: 4

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

You need group by and having

select Branch.BranchNo, COUNT(Member.MemberID) as "Number of Members" 
from Branch
group by Branch.BranchNo
having COUNT(Member.MemberID) > 2

Upvotes: 1

Related Questions