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
 COUNT and SUM in same Query

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2014-04-16 : 12:38:29
This should be simple but I can't quite figure out how to do it. I have searched but I suspect I don't know the proper phraseology to search for.

This query gives me part of what I want:

SELECT
TS_EXEC_STATUS as 'Status',
COUNT(*) as 'Total',
FROM TEST
WHERE TS_EXEC_STATUS <> ''
GROUP BY TS_EXEC_STATUS
ORDER BY TS_EXEC_STATUS

This query returns this:

Status Total
Failed 3
No Run 132
Not Completed 6
Passed 106

But I would like two more lines so the results are like this:

Status Total
Failed 3
No Run 132
Not Completed 6
Passed 106
Total 247
Percent Passed 42.9

I don't know how to get the last two lines with the Total of 247 and the Percent Passed of 42.9 (106/247).

Any help is appreciated.







kostya1122
Starting Member

15 Posts

Posted - 2014-04-16 : 13:45:55
you could try union all or (create a static table that has all
statuses including total and Percent Passed)

your query
union all
select 'Total' as [Status],
count(*) as total from test
union all

select
'Percent Passed' as [Status],
count(*) / sum(case when [Status Total] ='Passed' then 1 else 0 end) as total from test
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-16 : 14:00:07
There are a few ways you could do this. If the TEST table is not too big, something like this might work:


SELECT TS_EXEC_STATUS as 'Status'
, COUNT(*) as 'TOTAL'
, 0 as SORT_ORDER
FROM TEST
WHERE TS_EXEC_STATUS <> ''
GROUP BY TS_EXEC_STATUS

UNION ALL

SELECT 'Total'
, COUNT(*)
, 1
FROM TEST
WHERE TS_EXEC_STATUS <> ''

UNION ALL

SELECT 'Percent Passed'
, CAST( (SELECT COUNT(*) from TEST where TS_EXEC_STATUS = 'Passed') as FLOAT)
/ (SELECT COUNT(*) from TEST where TS_EXEC_STATUS <> '')
, 2

ORDER BY SORT_ORDER, TS_EXEC_STATUS


But I would suggest that you check out GROUP BY options, especially CUBE and ROLLUP. Also, have a look at windowing functions. For example, this query uses windowing to capture the count, grand total and ratio for each status in each row:


SELECT TS_EXEC_STATUS as 'Status'
, COUNT(*) over(partition by TS_EXEC_STATUS) as TOTAL
, COUNT(*) over() as GRAND_TOTAL
, CAST(COUNT(*) over(partition by TS_EXEC_STATUS) as FLOAT)
/ (COUNT(*) over ())
FROM TEST
WHERE TS_EXEC_STATUS <> ''
GROUP BY TS_EXEC_STATUS


You could use that as a subquery then pick out the bits you want to report on. Even nicer if you use it as a CTE, something like this:


;WITH StatusGroups as (
SELECT TS_EXEC_STATUS as 'Status'
, COUNT(*) over(partition by TS_EXEC_STATUS) as TOTAL
, COUNT(*) over() as GRAND_TOTAL
, CAST(COUNT(*) over(partition by TS_EXEC_STATUS) as FLOAT)
/ (COUNT(*) over ()) AS Ratio
FROM TEST
WHERE TS_EXEC_STATUS <> ''
GROUP BY TS_EXEC_STATUS
)

SELECT [Status], Total from StatusGroups
UNION ALL
SELECT TOP(1) 'Total', GRAND_TOTAL FROM StatusGroups
UNION ALL
SELECT TOP(1) 'Percent Passed', Ratio*100.0 FROM StatusGroups
WHERE [Status] = 'Passed'
Go to Top of Page

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-16 : 14:54:38
something like this may be...

DECLARE @Input TABLE
([Status] VARCHAR(20),
Total INT
)
INSERT INTO @Input VALUES('Failed', 3), ('No Run', 132), ('Not Completed', 6), ('Passed', 106)

;WITH CTE AS
(SELECT SUM(Total) AS Total FROM @Input)
SELECT [Status], CAST((Total*100*1.0/(SELECT Total FROM CTE)) AS DECIMAL(9,2)) AS [Percent Passed]
FROM @Input
WHERE [Status] = 'Passed'


Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-17 : 02:01:10
[code]
DECLARE @Temp TABLE(STATUS VARCHAR(100),TOTAL INT)
INSERT INTO @Temp VALUES('Failed',3),('No Run',132),('Not Completed',6),('Passed',106)
SELECT STATUS,TOTAL FROM @Temp
UNION ALL
SELECT 'Total' AS STATUS,SUM(Total)AS TOTAL FROm @Temp
UNION ALL
select DISTINCT 'Percent Passed' as [Status],
(CAST((SELECT TOTAL FROM @Temp WHERE STATUS = 'Passed')AS FLOAT)/CAST((SELECT SUM(Total)AS TOTAL FROm @Temp )AS FLOAT)*100) AS total FROM @Temp
[/code]

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -