counting rows and getting averages
-
I'm running a little sql report with ASP, and I was wondering if there's a way in SQL to count the number of rows returned based on the select statement.
For example, if I do a select * from tbl_socks, and get 6 results back. And 4 of those 6 results are blue socks, and the rest are red socks. Is there a way to display the total amount of red socks, and the total amount of blue socks? And do the same for other columns?
SockId Brand Type Size Color
-----------------------------------------------
1 Nike ankle 10 Blue
2 Goldtoe knee 7 Red
3 Jock knee 11 Blue
4 Reebok ankle 5 Blue
5 Nike calf 10 Red
6 Wilson calf 4 Blue
Total socks: 6
Total Blue: 4 percentage blue: 66%
Total Red: 2
Total ankle: 2
Total knee: 2 percentage knee: 33%
Total calf: 2
Is this possible?
Thanks!
-
You'd need separate queries, e.g.
SELECT Color, COUNT(*)
FROM tbl_socks
GROUP BY Color
SELECT Type, COUNT(*)
FROM tbl_socks
GROUP BY Type
The percentages are probably best applied by the ASP script.
-
It might help you to do research on CUBE building. This might help you get what you want. See BOL
Good Hunting!AJ Ahrens
webmaster@kritter.net -
You beat me to it AJ
CUBE is one of those little-used operators that is handy in giving what could effectively be viewed as a matrix of summary values with totals on each dimension.
From BOL
The CUBE operator generates a result set that is a multidimensional cube. A multidimensional cube is an expansion of fact data, or data that records individual events. The expansion is based on columns that the user wants to analyze. These columns are called dimensions. The cube is a result set containing a cross tabulation of all the possible combinations of the dimensions.
The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns and the keywords WITH CUBE. The result set contains all possible combinations of the values in the dimension columns, along with the aggregate values from the underlying rows that match that combination of dimension values.
For example, a simple table Inventory contains:
Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210This query returns a result set that contains the Quantity subtotal for all possible combinations of Item and Color:
SELECT Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBEHere is the result set:
Item Color QtySum -------------------- -------------------- -------------------------- Chair Blue 101.00 Chair Red 210.00 Chair (null) 311.00 Table Blue 124.00 Table Red 223.00 Table (null) 347.00 (null) (null) 658.00 (null) Blue 225.00 (null) Red 433.00The following rows from the result set are of special interest:
Chair (null) 311.00This row reports a subtotal for all rows having the value Chair in the Item dimension. The value NULL is returned for the Color dimension to show that aggregate reported by the row includes rows with any value of the Color dimension.
Table (null) 347.00This row is similar, but reports the subtotal for all rows having Table in the Item dimension.
(null) (null) 658.00This row reports the grand total for the cube. Both the Item and Color dimensions have the value NULL showing that all values of both dimensions are summarized in the row.
(null) Blue 225.00 (null) Red 433.00These two rows report the subtotals for the Color dimension. Both have NULL in the Item dimension to show that the aggregate data came from rows having any value for the Item dimension.
-
Thanks guys, using CUBE sounds like a wonderful solution!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply