英文:
Unable to convert varchar to int in SQL Server
问题
我有一个表格，其中包含如下数据。
我的要求是需要计算type=R和type=C时machineid的数量。
所以我创建了以下查询，但是我遇到了一个错误：
在将varchar值'gvpm236b'转换为int数据类型时转换失败。
我尝试了很多方法，如使用convert和cast，但都不起作用。
这是我的查询：
SELECT
COUNT (CASE WHEN Type = 'R' THEN CONVERT(int, MachineID) ELSE 0 END) AS 'Non-Persistant',
COUNT (CASE WHEN Type = 'C' THEN CONVERT(int, MachineID) ELSE 0 END) AS 'Persistant'
FROM
table
示例数据：
| MachineID | Type |
|---|---|
| gvpm236b | R |
| 123456 | C |
| 111 | C |
| 123 | R |
期望的输出：
| 非持久 | 持久 |
|---|---|
| 2 | 2 |
有人可以帮忙解决这个问题吗？
英文:
I have a table where I am having data like below.
My requirement is I need to calculate count of machineid when type=R and also when type=C.
So I created a query as shown later on, but I'm getting an error
> Conversion failed when converting the varchar value 'gvpm236b' to data type int.
I tried many ways like convert and cast but not working
This is my query :
SELECT
COUNT (CASE WHEN Type = 'R' THEN CONVERT(int, MachineID) ELSE 0 END) AS 'Non-Persistant',
COUNT (CASE WHEN Type = 'C' THEN CONVERT(int, MachineID) ELSE 0 END) AS 'Persistant'
FROM
table
Sample data :
| MachineID | Type |
|---|---|
| gvpm236b | R |
| 123456 | C |
| 111 | C |
| 123 | R |
Expected output:
| Non Persistant | Persistant |
|---|---|
| 2 | 2 |
Could any one please help how to fix this issue?
答案1
得分: 1
这对我来说没问题：
select count(distinct case when Type='R' then machineid END) AS r
, count(distinct case when Type='C' then machineid END) AS c
from (
VALUES (N'MachineID', N'Type')
, (N'gvpm236b', N'R')
, (N'gvpm236b', N'R')
, (N'123456', N'C')
, (N'111', N'C')
, (N'111', N'C')
, (N'123', N'R')
) t (machineid, type)
英文:
This works fine for me:
select count(distinct case when Type='R' then machineid END) AS r
, count(distinct case when Type='C' then machineid END) AS c
from (
VALUES (N'MachineID', N'Type')
, (N'gvpm236b', N'R')
, (N'gvpm236b', N'R')
, (N'123456', N'C')
, (N'111', N'C')
, (N'111', N'C')
, (N'123', N'R')
) t (machineid,type)
I added some dups just to test
答案2
得分: 0
由于您正在计算总数，因此无需将 MachineID 转换为整数：
SELECT
COUNT (case when Type='R' then MachineID end) as 'Non-Persistant',
COUNT (case when Type='C' then MachineID end) as 'Persistant'
FROM table
或者使用函数 sum()：
SELECT
SUM (case when Type='R' then 1 else 0 end) as 'Non-Persistant',
SUM (case when Type='C' then 1 else 0 end) as 'Persistant'
FROM table
如果只想计算不同的机器数量：
SELECT
COUNT (Distinct case when Type='R' then MachineID end) as 'Non-Persistant',
COUNT (Distinct case when Type='C' then MachineID end) as 'Persistant'
FROM table
英文:
Since you are counting totals, there is no need to convert MachineID to int:
SELECT
COUNT (case when Type='R' then MachineID end) as 'Non-Persistant',
COUNT (case when Type='C' then MachineID end) as 'Persistant'
FROM table
Or using the function sum() :
SELECT
SUM (case when Type='R' then 1 else 0 end) as 'Non-Persistant',
SUM (case when Type='C' then 1 else 0 end) as 'Persistant'
FROM table
To count distinct machines only then :
SELECT
COUNT (Distinct case when Type='R' then MachineID end) as 'Non-Persistant',
COUNT (Distinct case when Type='C' then MachineID end) as 'Persistant'
FROM table
答案3
得分: 0
select
sum ( case when Type='R' then 1 else 0 end ) as 'Non-Persistant',
sum ( case when Type='C' then 1 else 0 end ) as 'Persistant'
from ddddd
distinct Count
select count(distinct Non_Persistant),count(distinct Persistant)
from (
SELECT
(case when Type='R' then MachineID end) as 'Non-Persistant',
(case when Type='C' then MachineID end) as 'Persistant'
FROM ddddd
)s
Example Data:
create table ddddd(MachineID varchar(100), Type varchar(100))
insert into ddddd
values ('123456', 'C'),
('111', 'C'),
('123', 'R'),
('gvpm236b', 'R')
英文:
select
sum ( case when Type='R' then 1 else 0 end ) as 'Non-Persistant' ,
sum ( case when Type='C' then 1 else 0 end ) as 'Persistant'
from ddddd
distinct Count
select count(distinct Non_Persistant),count(distinct Persistant)
from (
SELECT
(case when Type='R' then MachineID end) as 'Non_Persistant',
(case when Type='C' then MachineID end) as 'Persistant'
FROM ddddd
)s
Example Data:
create table ddddd(MachineID varchar(100), Type varchar(100)
)
insert into ddddd
values ('123456' ,'C')
,('111' ,'C')
,('123' ,'R')
,('gvpm236b' ,'R')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论