Should be Simple - 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

Should be Simple

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-26 : 09:04:51

New to SQL Server Programming. This should be simple. Any help getting started is appreciated.

Have single table DEFECTS.
Need to report # defects by APPLICATION

Where ENVIRONMENT = "TST"
Number defects by SEVERITY (where SEVERITY <> "Enhancement")

Where ENVIRONMENT = "TRAIN"
Total # Defects

Example:

Test Environment Train Environment Grand Total
Application Crit High Medium Low All
----------- -------------------- ----------------- -----------
App 1 2 1 4 0 7 14
App 2 3 8 19 7 12 49

Grand Total 5 9 23 7 19 63

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 09:08:21

SELECT Application,
SUM(CASE WHEN Environment = 'TST' AND Severity = 'Crit' THEN 1 ELSE 0 END) AS Crit,
SUM(CASE WHEN Environment = 'Train' THEN 1 ELSE 0 END) AS Total
FROM Defects
GROUP BY Application
ORDER BY Application

N 56°04'39.26"
E 12°55'05.63"

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-26 : 09:56:21

Thanks. Was able to figure most of it out. I now have:

SELECT
BUG.BG_PROJECT as 'Team',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '1-Critical' THEN 1 ELSE 0 END) AS 'Critical',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '2-High' THEN 1 ELSE 0 END) AS 'High',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '3-Medium' THEN 1 ELSE 0 END) AS 'Medium',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '4-Low' THEN 1 ELSE 0 END) AS 'Low',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TRAIN' THEN 1 ELSE 0 END) AS 'All',
COUNT(*) AS 'Total'
FROM BUG
WHERE
BUG.BG_DETECTION_DATE BETWEEN '01/17/2010' AND '01/23/2010' AND
BUG.BG_STATUS <> 'Closed' AND
BUG.BG_STATUS <> 'Script Error'
GROUP BY BUG.BG_PROJECT
ORDER BY BUG.BG_PROJECT

... What I am missing now is:

1. I need column totals - like this:

Grand Total: 11 19 51 21 17 119

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 10:19:47

Add

UNION ALL

{Your query here again, replace bug.bg_project with NULL and drop GROUP BY line and ORDER BY line}

N 56°04'39.26"
E 12°55'05.63"

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-26 : 12:56:04

I've tried and tried and doing literally what you say, the query never stops running. I am probably misinterpreting what you are sayiing ... Here it is with the modifications you suggest:

UNION ALL
SELECT
NULL as 'Team',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '1-Critical' THEN 1 ELSE 0 END) AS 'Critical',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '2-High' THEN 1 ELSE 0 END) AS 'High',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '3-Medium' THEN 1 ELSE 0 END) AS 'Medium',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TST' AND BUG.BG_SEVERITY = '4-Low' THEN 1 ELSE 0 END) AS 'Low',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = 'TRAIN' THEN 1 ELSE 0 END) AS 'All',
COUNT(*) AS 'Total'
FROM BUG
WHERE
BUG.BG_DETECTION_DATE BETWEEN '01/17/2010' AND '01/23/2010' AND
BUG.BG_STATUS <> 'Closed' AND
BUG.BG_STATUS <> 'Script Error'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:59:35

are you doing this for displaying in some reports?

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-26 : 13:26:33

yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 13:27:43

quote:
Originally posted by planetoneautomation

yes

whats the report tool you're using?

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-26 : 13:48:13

Sorry - misunderstood ... not using a reporting tool - the data will be used for reporting but I have to have the column totals returned by SQL ... I will just be formatting

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 15:35:59

Try this intermediate solution. It does not have to read the full source again.

SELECT		BG_PROJECT AS Team,
		SUM(CASE WHEN BG_DETECTION_VERSION = 'TST' AND BG_SEVERITY = '1-Critical' THEN 1 ELSE 0 END) AS Critical,
		SUM(CASE WHEN BG_DETECTION_VERSION = 'TST' AND BG_SEVERITY = '2-High' THEN 1 ELSE 0 END) AS High,
		SUM(CASE WHEN BG_DETECTION_VERSION = 'TST' AND BG_SEVERITY = '3-Medium' THEN 1 ELSE 0 END) AS Medium,
		SUM(CASE WHEN BG_DETECTION_VERSION = 'TST' AND BG_SEVERITY = '4-Low' THEN 1 ELSE 0 END) AS Low,
		SUM(CASE WHEN BG_DETECTION_VERSION = 'TRAIN' THEN 1 ELSE 0 END) AS All,
		COUNT(*) AS Total
INTO		#Temp
FROM		BUG
WHERE		BG_DETECTION_DATE BETWEEN '01/17/2010' AND '01/23/2010'
		AND BG_STATUS <> 'Closed'
		AND BG_STATUS <> 'Script Error'
GROUP BY	BG_PROJECT

SELECT		COALESCE(Team, 'Grand total') AS Team,
		Critical,
		High,
		Medium,
		Low,
		Total
FROM		(
			SELECT	Team,
				Critical,
				High,
				Medium,
				Low,
				Total
			FROM	#Temp

			UNION ALL

			SELECT	NULL,
				SUM(Critical),
				SUM(High),
				SUM(Medium),
				SUM(Low),
				SUM(Total)
			FROM	#Temp
		) AS d
ORDER BY	CASE
			WHEN Team IS NULL THEN 1
			ELSE 0
		END,
		Team

DROP TABLE	#Temp

N 56°04'39.26"
E 12°55'05.63"

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-26 : 17:08:47

Doesn't like this:

INTO #Temp

I've tried every way I can to use a temp table but it doesn't like it. I don't mind reading the source again if it simpler.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 17:29:52

"Doesn't like..."? What tool do you use to execute the query?

N 56°04'39.26"
E 12°55'05.63"

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-27 : 07:45:06

HP Quality Center's "Excel Reports"

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-27 : 08:37:22

I found that both "WITH ROLLUP" or "WITH CUBE" added to the GROUP BY clause gives me the numbers I want. However, the results of the ROLLUP or CUBE is placed at the TOP of the rows of data instead of the bottom. Can this be controlled?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-27 : 08:38:44

Are you using SQL Server 2008? If so, add a GROUPING SET at the end of query.

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 09:01:02

If not wrap the query in an Outer SELECT * with a suitable ORDER BY ??

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-27 : 09:04:54

Unfortunately no - 2005 sp3

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-27 : 09:06:44

Not sure what you mean by "wrap the query in an Outer SELECT * with a suitable ORDER BY??"

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 11:45:18

[code]SELECT *
FROM
(
SELECT ... original query here ...
) AS X
ORDER BY ... fields to get correct order of ROLLUP data ...
[/code]
I haven't done ROLLUP for a while, but IIRC you have to set quite a complex ORDER BY with CASE statements to test for NULL values, on Group By column, to assign then, say, either 1 or 2 to sort low/high within that block.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-01-28 : 09:31:07

When I do what is suggested, the rollup does not move to the bottom ... it disappears altogether. Not sure if I've implemented correctly. Here's SQL:

SELECT * FROM
(
SELECT
BUG.BG_PROJECT as 'Application',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = '@Environment@' AND BUG.BG_SEVERITY = '1-Critical' THEN 1 ELSE 0 END) AS 'Critical',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = '@Environment@' AND BUG.BG_SEVERITY = '2-High' THEN 1 ELSE 0 END) AS 'High',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = '@Environment@' AND BUG.BG_SEVERITY = '3-Medium' THEN 1 ELSE 0 END) AS 'Medium',
SUM(CASE WHEN BUG.BG_DETECTION_VERSION = '@Environment@' AND BUG.BG_SEVERITY = '4-Low' THEN 1 ELSE 0 END) AS 'Low',
COUNT(*) AS 'Total'
FROM BUG
WHERE
BUG.BG_DETECTION_VERSION = '@Environment@' AND
BUG.BG_DETECTION_DATE BETWEEN '@BeginDate@' AND '@EndDate@' AND
BUG.BG_STATUS <> 'Closed' AND
BUG.BG_STATUS <> 'Script Error' AND
BUG.BG_SEVERITY <> '5-Enhancement'
GROUP BY BUG.BG_PROJECT WITH ROLLUP
) As X

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 09:34:06

Maybe I have mis-remembered, I thought ROLLUP totals were included in the same resultset, maybe they are in separate resultsets?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources