latox — 2010-09-29T11:49:15-04:00 — #1
I want to display on page:
Action (How many results Action has)
Adventure (How many results Adventure has)
So if Action had 15 results, adventure had 6, it'd show like this:
It only shows the NAME once, but I also want it to show how many of that name there are in the table.
How do I do a query like this?
ok_hornet — 2010-09-29T13:20:54-04:00 — #2
It's hard to tell from the code snippet, but I'm assuming the numbers should actually be under the ID title and that the Name column has multiple "Adventures" and multiple "Actions" like this
There are two ways that I know of to be able to count records with SQL, both involve the COUNT() function:
One is to count the Name field each time when the Name has the value of 'Action' or 'Adventure' individually.
SELECT COUNT(Name) FROM tblName WHERE Name='Action';
SELECT COUNT(Name) FROM tblName WHERE Name='Adventure';
The other is to use COUNT(DISTINCT columnName):
SELECT COUNT(DISTINCT ID) FROM tblName WHERE Name='Action';
SELECT COUNT(DISTINCT ID) FROM tblName WHERE Name='Adventure';
Both should get you the numbers you're looking for with separate queries.
If you're looking for one query to do both and then are writing the values out with ASP:
rs.open "SELECT ID, Name FROM tblName;", conn
dim act, adv
act = 0
adv = 0
do while not rs.EOF
if rs.Fields("Name")="Action" then
act = act + 1
if rs.Fields("Name")="Adventure" then
adv = adv + 1
Action = <%=act%>
Adventure = <%=adv%>
<% rs.close %>
If you want to use PHP (I believe this is how to do it):
$sqlAdv = mysql_query("SELECT ID FROM table WHERE Name='Adventure'");
$sqlAct = mysql_query("SELECT ID FROM table WHERE Name='Action'");
$countAdv = mysql_num_rows($sqlAdv);
$countAct = mysql_num_rows($sqlAct);
Action = <?=$act?>
Adventure = <?=$adv?>
These are my best guesses. I am personally using the ASP version I gave you. The PHP process is strictly an informed guess-timate.
latox — 2010-09-29T13:15:21-04:00 — #3
Nevermind, I worked it out, forgot about DISTINCT !
ok_hornet — 2010-09-29T14:56:12-04:00 — #4
Never knew you could do that. Should come in handy!
ok_hornet — 2010-09-29T13:21:46-04:00 — #5
Must have just missed your reply.
r937 — 2010-09-29T13:28:57-04:00 — #6
separate queries? no, there's a much easier way
, COUNT(*) AS ids