Query Total Help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Total Help

Author

Topic

Granick
Starting Member

46 Posts

Posted - 2002-08-28 : 18:20:16

Assuming I need have the followin in a table, although I only included a few rows for simplicity

Table looks something like this:

Group1 2
Group1 2
Group1 5
Group1 4
Group2 1
Group2 2
Group2 4
Group3 2
Group3 5

Result looks like this:

Grouping Type1 Type2 Type3 Type4 Type5 Group Total
Group1 0 20 0 42 5 67
Group2 1 3 11 17 0 32
Group3 0 2 0 0 1 3

I have a single query that will return the results as shown above. But I need to get the total NON-GROUPED. This will be used so I can get a percentage value for each row. Because of the dynamic nature of the request, we let the user select the filter fields, it makes using subqueries, much more difficult.

In the above example, this number would be 102. Since the base table has other fields, which I didn't include, but are used for filtering, I can not figure out a way to use a subquery, etc.

Any help would be greatly appreciated.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-28 : 18:48:41

Maybe something like this:

 
CREATE TABLE #MyTable(GroupName VARCHAR(50), Type INT)

INSERT INTO #MyTable(GroupName, Type) VALUES('Group1', 2)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group1', 2)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group1', 5)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group1', 4)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group2', 1)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group2', 2)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group2', 4)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group3', 2)
INSERT INTO #MyTable(GroupName, Type) VALUES('Group3', 5)

SELECT 
	(SELECT COUNT(*) FROM #MyTable WHERE Type=1) + 
	(SELECT COUNT(*) FROM #MyTable WHERE Type=2) +
	(SELECT COUNT(*) FROM #MyTable WHERE Type=3) +
	(SELECT COUNT(*) FROM #MyTable WHERE Type=4) +
	(SELECT COUNT(*) FROM #MyTable WHERE Type=5) As TotalForAllGroups

DROP TABLE #MyTable

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources