Help with SQL - 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

Help with SQL

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-17 : 08:09:52

No quite sure how to construct a single SQL statement to accomplish this:

1. There is only one table involved: BUG
2. I want to report percentages of rows by BUG.BG_PROJECT by BUG.BG_SEVERITY against total rows.
3. I want to report percentages by BUG.BG_SEVERITY against total rows.

Here is sample output:

[CODE]
ProjectName %Critical %High %Medium %Low Total Defects

<TOTALS> 15% 10% 50% 25% 30
Project1 11% 8% 28% 19% 20
Project2 4% 2% 22% 6% 10
[/CODE]

The <TOTALS> line percentages add to 100%
The percentages for all projects add up to to 100%
The total project defects (20 + 10) equals the total defects (30)
The total project severity percentages add up to the <TOTALS> severity percentage (e.g. for Critical, TOTAL percentage is 15% and the project percentages (11% + 4%) add up to 15%.

Any help is appreciated.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 08:28:13

sample data as well would be good!

We don't know all the details of your table and how the data looks now.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-17 : 08:41:21

I provided sample data above as well as the table name and the fields involved. Not sure what else I could provide that would be helpful.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-02-17 : 08:50:39

Follow the link in my signature on How To Ask. It shows you how to provide DDL, DML, and everything else we would need to help you solve your problem.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 08:51:14

well You've given us sample output.

Is the data like this in the table:


Project1      11%           8%    28%       19%    20
Project2       4%           2%    22%        6%    10

(that's just what the data looks like -- there'x no sum or aything used to get that?)

And you just want a row that sums all the values at the top?

Then you could use UNION

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 09:00:30

Maybe?


SELECT
	r.[projectName]
	, r.[%Critical]
	, r.[%High]
	, r.[%Medium]
	, r.[%Low]
	, r.[Total Defects]
FROM
	(
	SELECT
		1 AS [rank]
		, [projectName]
		, [%Critical]
		, [%High]
		, [%Medium]
		, [%Low]
		, [Total Defects]
	FROM
		bug
	UNION ALL SELECT
		0 AS [rank]
		, '<TOTAL>' AS [projectName]
		, SUM([%Critical]) AS [%Critical]
		, SUM([%High]) AS [%High]
		, SUM([%Medium]) AS [%Medium]
		, SUM([%Low]) AS [%Low]
		, SUM([Total Defects]) AS [Total Defects]
	FROM
		bug
	)
	AS r
ORDER BY
	r.[rank] ASC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-17 : 09:01:46

The sample output:
[CODE]
ProjectName %Critical %High %Medium %Low Total Defects

<TOTALS> 15% 10% 50% 25% 30
Project1 11% 8% 28% 19% 20
Project2 4% 2% 22% 6% 10
[/CODE]
For this output, it means that in the table BUG there are 30 rows. Of the 30 rows, 20 of the rows have BUG.BG_PROJECT = "Project1" and 10 of the rows have BUG.BG_PROJECT = "Project2".

For Project1, 11% of the 20 rows have BUG.BG_SEVERITY = Critical, 8% BUG.BG_SEVERITY = High, 28% BUG.BG_SEVERITY = Medium and 19% BUG.BG_SEVERITY = Low.

For Project2, 4% of the 10 rows have BUG.BG_SEVERITY = Critical, 2% BUG.BG_SEVERITY = High, 22% BUG.BG_SEVERITY = Medium and 6% BUG.BG_SEVERITY = Low.

** Since the percentages in this example are made up for illustration purposes, they may not be correct (e.g. 11% of 20 rows does not come out to a whole row - but I think the picture is clear).

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-17 : 09:03:39

Maybe this thread is nice to know for all helping mates.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138864&whichpage=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources