I have a table with below format :
| Column A | Column B | column C | column D |
|---|---|---|---|
| 5 | 2 | A1 | 99 |
| 5 | 2 | A2 | 99 |
| 5 | 2 | Group | 99 |
| 5 | 2 | A3 | 98 |
| 7 | 6 | D | 99 |
| 7 | 3 | D | 97 |
| 9 | 1 | E | 90 |
Need the following output using sql query
| Column B Count[Distinct] | Column C Count | No of rows where Column D Count >= 98 | No of rows where Column D Count < 98 |
|---|---|---|---|
| 4 | 6 | 2 | 2 |
Note :
if it is a Group fetch the value of Column D whose Column C value is
'Group' otherwise fetch the single record value
hi
hope this helps
drop table #Data
create table #Data (ColumnA int, ColumnB int, ColumnC varchar(10), ColumnD int)
insert into #Data select 5, 2,'A1' ,99
insert into #Data select 5, 2,'A2' ,99
insert into #Data select 5, 2,'Group' ,99
insert into #Data select 5, 2,'A3' ,98
insert into #Data select 7, 6,'D' ,99
insert into #Data select 7, 3,'D' ,97
insert into #Data select 9, 1,'E' ,90
SELECT
count(distinct ColumnB)
, count(distinct case when ColumnC = 'Group' then cast(ColumnD as varchar) else ColumnC end)
, count(distinct case when ColumnD > = 98 then ColumnD end)
, count(distinct case when ColumnD < 98 then ColumnD end)
FROM
#Data
Hello Sir
Thank you for the query. It works for the above sample but when I change the data I am not able to achieve the desired result.
Suppose if the new data is like below :
| Column A | Column B | column C | column D |
|---|---|---|---|
| 5 | 2 | A1 | 9 |
| 5 | 2 | A2 | 97 |
| 5 | 2 | Group | 9 |
| 5 | 2 | A3 | 94 |
| 7 | 6 | D | 90 |
| 7 | 3 | D | 90 |
| 9 | 1 | E | 9 |
Now instead of 98%, If I fetch for 95% , then
When I use your query I get the below result :
| Column B Cnt | Column C Cnt | column D > 95% | column D < 95% |
|---|---|---|---|
| 4 | 6 | 1 | 3 |
But the result I'm expecting is :
| Column B Cnt | Column C Cnt | column D > 95% | column D < 95% |
|---|---|---|---|
| 4 | 6 | 0 | 4 |
Because when the records are part of the group (in this example Column A : First 4 records), we need to consider Column D value for the row whose column C='Group' , so the value is '9' which is less than 95.
hi
hope this helps
i was getting extremely confused here
Open AI = o3-mini ( copy pasted her whole reply into this ) ( clicked a submit button 2 seconds this is what it generated )
any thoughts opinions debates ?? anything and everything
drop table #Data
create table #Data (ColumnA int, ColumnB int, ColumnC varchar(10), ColumnD int)
insert into #Data select 5,2,'A1',9
insert into #Data select 5,2,'A2',97
insert into #Data select 5,2,'Group',9
insert into #Data select 5,2,'A3',94
insert into #Data select 7,6,'D',90
insert into #Data select 7,3,'D',90
insert into #Data select 9,1,'E',9
;WITH cte
AS (SELECT [columna],
[columnb],
[columnc],
[columnd],
Count(CASE
WHEN [columnc] = 'Group' THEN 1
END)
OVER(
partition BY [columna], [columnb]) AS GroupRowCount
FROM #data),
consolidated
AS (SELECT [columna],
[columnb],
[columnc],
[columnd]
FROM cte
WHERE grouprowcount = 0
UNION ALL
SELECT [columna],
[columnb],
[columnc],
[columnd]
FROM cte
WHERE grouprowcount > 0
AND [columnc] = 'Group')
SELECT (SELECT Count(DISTINCT [columnb])
FROM #data) AS [ColumnB Cnt],
(SELECT Count(DISTINCT [columnc])
FROM #data) AS [ColumnC Cnt],
Sum(CASE
WHEN [columnd] > 95 THEN 1
ELSE 0
END) AS [columnD > 95%],
Sum(CASE
WHEN [columnd] < 95 THEN 1
ELSE 0
END) AS [columnD < 95%]
FROM consolidated;
Yes thank you this helped. However I have one question : If I need to join another table here where should I put the code : In the cte ? or last SELECT Statement ?
you can Put it in any of the places
in the CTE
or
Last Select
depending on what you are trying to do
