Sorry, I haven't rightly understood your question at first.
According with Dan Bracuk but in plain SQL:
var rows = db.Query(@"SELECT Category, COUNT(*) AS Rows FROM YourDb GROUP BY Category
UNION SELECT 'Total' AS Category, COUNT(*) AS Rows FROM YourDb");
Sorry not explaining this proerply. OK I have a stats.cshtml page and I basically want to know how many pages (rows) are in category CSS, how many pages are in category HTML etc. It can be in a list view or table. Then I want to add the pages in HTML, CSS,
C#, VB, ASP, PHP together.
I'm not sure to understand, so I will try with an example.
You have a table (Pages) like this one:
Id Name Category
1 First Css Page CSS
2 An Html page HTML
3 Another Html Page HTML
4 This page is about C# C#
5 Second Css Page CSS
6 Yet another Html Page HTML
This page should achieve what you are seaching for:
@{
var db = Database.Open("YourDb");
var data = db.Query("SELECT Category, COUNT(*) AS RowNumber FROM Pages GROUP BY Category");
var counter = 0;
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
</head>
<body>
<ol>
@foreach(var row in data){
<li>Pages in @row.Category: @row.RowNumber</li>
counter += row.RowNumber;
}
<li>Total: @counter</li>
</ol>
</body>
</html>
var rows = db.Query(@"SELECT Category, COUNT(*) AS Rows FROM YourDb GROUP BY Category
UNION SELECT 'Total' AS Category, COUNT(*) AS Rows FROM YourDb");
To the charge of over-engineering, I plead guilty.
CriticalErro...
Member
421 Points
390 Posts
How to count rows in database depending on category?
Nov 26, 2012 10:09 PM|LINK
How can I count the number of rows depeding on the category. For example count the number of rows the category HTML has.
I was this info to be on a page and also the total columns in all the categorys.
My Site | My Blog
mebinici
Participant
815 Points
256 Posts
Re: How to count rows in database depending on category?
Nov 26, 2012 10:17 PM|LINK
In a GridView or ListView? Can you provide more information? Do you want to return a datarow count?
Love collecting video games, movies and board games!
Enjoying my '11 WRX, so sexy...
GmGregori
Contributor
5440 Points
731 Posts
Re: How to count rows in database depending on category?
Nov 26, 2012 10:27 PM|LINK
You can achieve the number of rows for one category (e.g. 'HTML') from:
var rows = db.QueryValue("SELECT COUNT(*) FROM YourDb WHERE Category = @0", "HTML");The total number of rows simply from:
var rows = db.QueryValue("SELECT COUNT(*) FROM YourDb");CriticalErro...
Member
421 Points
390 Posts
Re: How to count rows in database depending on category?
Nov 26, 2012 10:48 PM|LINK
Oh table is probably best.
My Site | My Blog
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: How to count rows in database depending on category?
Nov 27, 2012 12:56 AM|LINK
something like this should work
with CategoryCounts as (
select category, count(*) recordcount
from etc
group by category
)
select category, recordcount records
from CategoryCounts
union
select 'total' category, sum(recordcount) records
from CategoryCounts
group by 'total'
GmGregori
Contributor
5440 Points
731 Posts
Re: How to count rows in database depending on category?
Nov 27, 2012 07:18 AM|LINK
Sorry, I haven't rightly understood your question at first.
According with Dan Bracuk but in plain SQL:
var rows = db.Query(@"SELECT Category, COUNT(*) AS Rows FROM YourDb GROUP BY Category UNION SELECT 'Total' AS Category, COUNT(*) AS Rows FROM YourDb");CriticalErro...
Member
421 Points
390 Posts
Re: How to count rows in database depending on category?
Nov 27, 2012 10:01 AM|LINK
Sorry not explaining this proerply. OK I have a stats.cshtml page and I basically want to know how many pages (rows) are in category CSS, how many pages are in category HTML etc. It can be in a list view or table. Then I want to add the pages in HTML, CSS, C#, VB, ASP, PHP together.
So it can be in a list view like this;
My Site | My Blog
GmGregori
Contributor
5440 Points
731 Posts
Re: How to count rows in database depending on category?
Nov 27, 2012 12:07 PM|LINK
I'm not sure to understand, so I will try with an example.
You have a table (Pages) like this one:
Id Name Category
1 First Css Page CSS
2 An Html page HTML
3 Another Html Page HTML
4 This page is about C# C#
5 Second Css Page CSS
6 Yet another Html Page HTML
This page should achieve what you are seaching for:
@{ var db = Database.Open("YourDb"); var data = db.Query("SELECT Category, COUNT(*) AS RowNumber FROM Pages GROUP BY Category"); var counter = 0; } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title></title> </head> <body> <ol> @foreach(var row in data){ <li>Pages in @row.Category: @row.RowNumber</li> counter += row.RowNumber; } <li>Total: @counter</li> </ol> </body> </html>Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: How to count rows in database depending on category?
Nov 27, 2012 07:39 PM|LINK
To the charge of over-engineering, I plead guilty.