June 12, 2017 at 3:15 pm
Hi..I'm trying to fetch some details but the counts aren't coming properly,can someone pls help with the query?
| State | BCNT | ECNT |
| IL | 103 | 73 |
| NV | 103 | 73 |
| OR | 103 | 73 |
| WA | 103 | 73 |
I need individual count for two things BCNT and ECNT ,but it's throwing total count for all the rows like above i.,e Total count of BCNT is 103 ,it has items from IL,NV,OR and WA,similarly for ECNT. Can anyone help me in getting individual counts?Data resides in one table
Below is the query
SELECT
A.Custom2 as State,
BCNT = (Select COUNT(*) FROM IM_V_Defects A where A.Defecttype = 'BUG' and A.ProjectName = 'Billing' ' and A.Status <> 'Closed'),
ECNT = (Select COUNT(*) FROM IM_V_Defects A where A.Defecttype = 'ENHANCEMENT' and A.ProjectName = 'Billing' and A.Status <> 'Closed')
FROM IM_V_Defects A
WHERE A.ProjectName = ' Billing' a A.Status <> 'Closed' and A.Defecttype IN ('BUG','ENHANCEMENT')
GROUP BY A.Custom
June 12, 2017 at 3:25 pm
SELECT
A.Custom2 as State,
BCNT = SUM(case when A.Defecttype = 'BUG' and A.ProjectName = 'Billing' ' and A.Status <> 'Closed' then 1 else 0 end),
ECNT = SUM(case when A.Defecttype = 'ENHANCEMENT' and A.ProjectName = 'Billing' and A.Status <> 'Closed' then 1 else 0 end)
FROM IM_V_Defects A
WHERE (A.Defecttype = 'BUG' and A.ProjectName = 'Billing' ' and A.Status <> 'Closed' ) or (A.Defecttype = 'ENHANCEMENT' and A.ProjectName = 'Billing' and A.Status <> 'Closed')
GROUP BY A.Custom
June 12, 2017 at 3:26 pm
The problem is that the subqueries are asking for exactly what you're getting: just the total count, no matter what the value of A.Custom.
There are a couple ways, one I will include only to show the minor change you could make; I would not recommend using that query form.
The not-recommended query form would be the smallest change. Your current query could be changed slightly so that the subqueries also have in the WHERE clause a condition that the subquery's Custom should match the parent query's Custom. You'd want to alias them differently, so you could see that.
However, I only say that to show the smallest change to the query to fix it.
Much better is to cut the use of subqueries altogether. Also, since this new approach is not using subqueries, you don't have to repeat all the WHERE clause criteria when determining what to count, since those criteria are already in the WHERE clause..
SELECT
A.Custom2 as State,
BCNT = SUM (CASE WHEN A.Defecttype = 'BUG' THEN 1 ELSE 0 END),
ECNT = SUM(CASE WHEN A.Defecttype = 'ENHANCEMENT' THEN 1 ELSE 0 END)
FROM IM_V_Defects A
WHERE A.ProjectName = 'Billing' AND A.Status <> 'Closed' AND A.Defecttype IN ('BUG','ENHANCEMENT')
GROUP BY A.Custom2
Cheers!
EDIT: Upon re-reading the thread with the newest responses, noticed that I'd left the GROUP BY from the original query, which was grouping by A.Custom, not A.Custom2. Seems it should be the latter, since that's what is being displayed as the 'State' column.
June 19, 2017 at 9:09 am
hemanth329 - Monday, June 12, 2017 3:15 PMHi..I'm trying to fetch some details but the counts aren't coming properly,can someone pls help with the query?
State BCNT ECNT IL 103 73 NV 103 73 OR 103 73 WA 103 73 I need individual count for two things BCNT and ECNT ,but it's throwing total count for all the rows like above i.,e Total count of BCNT is 103 ,it has items from IL,NV,OR and WA,similarly for ECNT. Can anyone help me in getting individual counts?Data resides in one table
Below is the query
SELECT
A.Custom2 as State,
BCNT = (Select COUNT(*) FROM IM_V_Defects A where A.Defecttype = 'BUG' and A.ProjectName = 'Billing' ' and A.Status <> 'Closed'),
ECNT = (Select COUNT(*) FROM IM_V_Defects A where A.Defecttype = 'ENHANCEMENT' and A.ProjectName = 'Billing' and A.Status <> 'Closed')
FROM IM_V_Defects A
WHERE A.ProjectName = ' Billing' a A.Status <> 'Closed' and A.Defecttype IN ('BUG','ENHANCEMENT')
GROUP BY A.Custom
I'm not sure you need to do much more than change your GROUP BY to be GROUP BY A.Custom2. If the Custom field is different from the Custom2 field, especially if it's values are all NULL, that would cause the same issue. However, you will need to change your sub-queries to just case statements and SUM functions. Here's what I think this should look like:
SELECT A.Custom2 AS [State],
SUM(CASE A.Defecttype WHEN 'BUG' THEN 1 ELSE 0 END) AS BCNT,
SUM(CASE A.Defecttype WHEN 'ENHANCEMENT' THEN 1 ELSE 0 END) AS ECNT
FROM IM_V_Defects A
WHERE A.ProjectName = ' Billing'
AND A.[Status] <> 'Closed'
AND A.Defecttype IN ('BUG','ENHANCEMENT')
GROUP BY A.Custom2;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2017 at 9:19 am
No need to add subqueries when you have the information available by reading the table once.
SELECT
A.Custom2 as State,
BCNT = COUNT(CASE WHEN A.Defecttype = 'BUG' THEN 1 END),
ECNT = COUNT(CASE WHEN A.Defecttype = 'ENHANCEMENT' THEN 1 END)
FROM IM_V_Defects A
WHERE A.ProjectName = ' Billing'
AND A.Status <> 'Closed'
and A.Defecttype IN ('BUG','ENHANCEMENT')
GROUP BY A.Custom2;
June 23, 2017 at 6:08 am
try this
;with data_cte
as (
SELECT
A.Custom2 as State,
Case
when A.Defecttype = 'BUG' then 1 else 0
end as BCNT,
Case
when A.Defecttype = 'ENHANCEMENT' then 1 else 0
end as ECNT
FROM IM_V_Defects A
WHERE A.ProjectName = ' Billing'
and A.Status <> 'Closed'
and A.Defecttype IN ('BUG','ENHANCEMENT')
GROUP BY A.Custom
)
Select
State,
SUM(BCNT) as BCNT,
SUM(BCNT) as ECNT
from data_cte a
Group by
State
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply