返回SQL使用COUNT和CASE相关表 - 错误 [英] SQL using COUNT and CASE for related table - error is returned

问题描述

我有表T1：

  ID GROUPINFO状态
 1 GROUP1新
 2 GROUP1 INPROG 
 3 GROUP2 INPROG 
  

我也有表T2也

  T2ID T1ID状态
 1 1新
 2 2新
 3 2 VENDOR 
 4 3新
 5 3 VENDOR 
  

我想按组中的数量计算多少条记录的状态为NEW，以及多少这些记录处于状态VENDOR（T2表中的信息）

此SQL工作

  SELECT T1.GROUPINFO，
 count（当T1.status ='NEW'然后1结束时的情况）为新
 FROM T1 
 GROUP BY T1.GROUPINFO 
  

但是，当我尝试添加这些记录中有多少在VENDOR状态时，我收到一个错误： p>

  SELECT T1.GROUPINFO，
 count（当T1.status ='NEW'然后1结束时的情况）a s新
计数（当T1.ID在（从T2.status ='VENDOR'中选择T2.ID）然后1结束）的情况下）作为供应商
从T1 
 GROUP BY T1我有一个错误：
 
 
 
 p>坏计划未解决的QNC发现
 
 
 
我不知道我做错了什么，我应该如何组织我的SQL查询，所以我可以得到这个结果这个例子：
  GROUPINFO NEW VENDOR 
 GROUP1 1 1 
 GROUP2 0 1 
  
如果在T2中存在多次ID，则应该只计算1次，因为这是一个记录，我想检查它是否处于该状态。 / p> 
 
 
更新：
我也尝试过EXISTS：
  SELECT T1.GROUPINFO，
计数（当T1.status ='NEW'然后1结束时的情况）为New，
计数（存在的情况（从T2选择1，其中T2.ID = T1.ID和T2 .status ='VENDOR'）then 1 end）as Vendor 
 FROM T1 
 GROUP BY T1.GROUPINFO 
  
但再次出现SQL错误：关于ID列
 
 
 
更新：
我也尝试过EXISTS和JOIN：
  SELECT T1.GROUPINFO，
 count（T1.status = NEW'then 1 end）as New，
 count（case when exists（select 1 from T2 where T2.ID = T1.ID and T2.status ='VENDOR'）then 1 end）as Vendor 
 FROM T1 LEFT OUTER JOIN T2 ON T1.ID = T2.ID 
 GROUP BY T1.GROUPINFO 
  
但再次出现SQL错误：关于ID列
解决方案
 
 选择T3。 Groupinfo 
计数（distinct（当t3.status =NEWthen t3.ID结束时的情况））作为新的
计数（distinct（如果t3.status_1 =VENDOR则为t3.id结束））作为卖方
从
（select t1.groupinfo，t1.id，t1.status，t2.t2id as id1，t2.status as status_1 from 
 t1 join t2 
 on t1.id = t2.t1id 
）as t3 
 group by t3.Groupinfo; 
  
与此类似的内容可能会帮助您，我猜。
 
I have table T1:
ID  GROUPINFO    STATUS
1   GROUP1       NEW
2   GROUP1       INPROG
3   GROUP2       INPROG
I  have table T2 also
T2ID    T1ID  STATUS
 1       1      NEW
 2       2      NEW
 3       2      VENDOR
 4       3      NEW
 5       3      VENDOR
I want to count by group how many of those records have the status NEW, and how many of those records were in the status VENDOR (information from T2 table)

This SQL works
SELECT T1.GROUPINFO,
count (case when T1.status='NEW' then 1 end) as New
FROM T1
GROUP BY T1.GROUPINFO
However when I try to add how many of those records were in the VENDOR status I am getting an error:
SELECT T1.GROUPINFO,
count (case when T1.status='NEW' then 1 end) as New,
count (case when T1.ID in (select T2.ID from T2 where T2.status='VENDOR') then 1 end) as Vendor
FROM T1
GROUP BY T1.GROUPINFO
I am getting an error:

Bad Plan; Unresolved QNC found

I am not sure what I am doing wrong and how should I organize my SQL query so I could get this result for this example:
GROUPINFO   NEW   VENDOR
GROUP1       1     1
GROUP2       0     1
If ID exists multiple times in T2 it should only be counted 1 time because this is one record for which I want to check if it was in that status or not.

Update:
I also tried EXISTS:
SELECT T1.GROUPINFO,
count (case when T1.status='NEW' then 1 end) as New,
count (case when exists (select 1  from T2 where T2.ID=T1.ID and T2.status='VENDOR') then 1 end) as Vendor
FROM T1
GROUP BY T1.GROUPINFO
but again having an SQL error: regarding the ID column

Update:
I also tried EXISTS and JOIN:
SELECT T1.GROUPINFO,
count (case when T1.status='NEW' then 1 end) as New,
count (case when exists (select 1  from T2 where T2.ID=T1.ID and T2.status='VENDOR') then 1 end) as Vendor
FROM T1 LEFT OUTER JOIN T2 ON T1.ID=T2.ID
GROUP BY T1.GROUPINFO
but again having an SQL error: regarding the ID column
解决方案
select T3.Groupinfo,
count (distinct(case when t3.status="NEW" then t3.ID end)) as new,
count (distinct(case when t3.status_1 ="VENDOR" then t3.id end)) as Vendor
from 
(select t1.groupinfo,t1.id,t1.status,t2.t2id as id1,t2.status as status_1 from
t1 join t2
on t1.id=t2.t1id
) as t3
group by t3.Groupinfo;
Something Similar to this might help you i guess.

                        
这篇关于返回SQL使用COUNT和CASE相关表 - 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！