Group By Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By Question

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-10-09 : 16:16:30

Here's the query:


SELECT
TC_STATUS AS 'Status', COUNT(*) as 'Total'
FROM
TESTCYCL
JOIN TEST ON TS_TEST_ID = TC_TEST_ID
JOIN RELEASE_CYCLES ON TC_ASSIGN_RCYC = RCYC_ID
WHERE RCYC_NAME = '(PQ) Test Cycle 3' AND TEST.TS_NAME Like '3.%'
GROUP BY TC_STATUS
ORDER BY TC_STATUS

Here's the question:

This query might return up to 5 different values for TC_STATUS. But it might return no rows or rows for one value or two values or three values or four values.

For example, what returns might have all possible status values:

STATUS .............TOTAL
Not Covered ....... 8
No Run .............10
Not Completed ......23
Passed .............118
Failed .............25

Or, maybe what's returned contains only four of the five possible:

STATUS .............TOTAL
Not Covered ....... 8
No Run .............10
Passed .............118
Failed .............25

Or, even none of the possible values.

What I want is, a row returned for every status regardless of whether it has a value or not - like this:

STATUS .............TOTAL
Not Covered ....... 0
No Run .............10
Not Completed ......0
Passed .............118
Failed .............25

In the above, no rows are returned for Not Covered or for Not Completed but I want to write the status value and the count of zero regardless.

Is this possible?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-09 : 16:25:13

Try using a LEFT JOIN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

kostya1122
Starting Member

15 Posts

Posted - 2014-10-09 : 17:55:21

you could try something like this

create table #temp
(col1 varchar(30))

insert into #temp values('STATUS')
insert into #temp values('Not Covered')
insert into #temp values('No Run')
insert into #temp values('Not Completed')
insert into #temp values('Passed')
insert into #temp values('Failed')

SELECT
TC_STATUS AS 'Status', COUNT(*) as 'Total'

into #temp2

FROM
TESTCYCL
JOIN TEST ON TS_TEST_ID = TC_TEST_ID
JOIN RELEASE_CYCLES ON TC_ASSIGN_RCYC = RCYC_ID
WHERE RCYC_NAME = '(PQ) Test Cycle 3' AND TEST.TS_NAME Like '3.%'
GROUP BY TC_STATUS
ORDER BY TC_STATUS

select x.col1 as [status], y.Total
from #temp as x
left join #temp2 as y
on x.col1 = y.[Status]

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2014-10-10 : 09:13:27

As tkizer said try with left join and keep table which contain "TC_STATUS" column on left side of join.

M.MURALI kRISHNA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources