SQLTeam.com | Weblogs | Forums

Union Queries with totals, data from same table

#1

Hi, I am trying to combine 2 queries that are counting records, but the output is confusing me.

The queries, individually, simply count how many records match a criteria, and they work individually. But when I combine them via union, the totals are changing. I have tried union all. The data for both queries is in the same table.

Basically, my query is counting how many people have done training, vs how many people havent, and I need it in a 2 column, 2 row format. I need it in this format for an Apex chart. The criteria is literally the opposite of each other.

  SELECT 'Completed' AS TEXT

      ,   COUNT(*) AS TOTAL

  FROM  table1

  WHERE training = 'Completed'

   

  UNION ALL

  SELECT 'Not Completed' AS TEXT

      ,  COUNT(*) AS TOTAL

  FROM  table1

  WHERE training != 'Completed'    indent preformatted text by 4 spaces
#2

Looks fine when i do it from Transact SQL .. SSMS Editor

Maybe your software ?? glitch .. or may be 100 different things
As usual have to debug being software people
Pain in the ....

drop table #table1 

create table #table1
(
Training varchar(100)
)

insert into #table1 select 'Completed'
insert into #table1 select 'Completed'
insert into #table1 select 'xxxx'
insert into #table1 select 'yy'
insert into #table1 select 'Completed'

select 'Sample Data',* from #table1 

SELECT 'Completed' AS TEXT      ,   COUNT(*) AS TOTAL  FROM  #table1  WHERE training = 'Completed'
SELECT 'Not Completed' AS TEXT      ,  COUNT(*) AS TOTAL  FROM  #table1  WHERE training != 'Completed'    

SELECT 'Completed' AS TEXT      ,   COUNT(*) AS TOTAL  FROM  #table1  WHERE training = 'Completed'
UNION ALL
SELECT 'Not Completed' AS TEXT      ,  COUNT(*) AS TOTAL  FROM  #table1  WHERE training != 'Completed'

image

#3

I suspect you are getting confused with 3 value logic when training is NULL:

CREATE TABLE #t
(
	training varchar(20) NULL
);
INSERT INTO #t
VALUES('Completed'),('Start'),(NULL),('End');

select * from #t;

SELECT COUNT(*)
FROM #t
WHERE training = 'Completed';

SELECT COUNT(*)
FROM #t
WHERE training != 'Completed';

SELECT COUNT(*)
FROM #t
WHERE training != 'Completed'
	OR training IS NULL;

You probably want:

SELECT X.[Text]
	,COUNT(1) AS Total
FROM #t T
	CROSS APPLY
	(
		VALUES
		(
			CASE
				WHEN T.training = 'Completed'
				THEN 'Completed'
				ELSE 'Not completed'
			END
		)
	) X ([Text])
GROUP BY X.[Text]
#4

Maybe I am missing something - but this seems to be a simple grouping:

Select text = Case When t.Training = 'Completed' Then 'Completed' Else 'Not Completed' End
     , total = count(*)
  From table1 t
 Group By
       Case When t.Training = 'Completed' Then 'Completed' Else 'Not Completed' End;
#5

For some reason, that produces totals that are way off, more than triple what they should be. I believe I have found a solution and it involves the unpivot function.

#6

That does not make sense - unless there is more to your queries than you have listed this is a simple grouping/count and cannot 'triple' the numbers.

You now are saying UNPIVOT will work...how is that going to help?