How do I do this query?

Databases
1 

			ID      Name
			3	Action
			13	Action
			16	Action
			24	Action
			26	Action
			32	Action
			35	Action
			40	Action
			44	Action
			76	Action
			95	Action
			112	Adventure
			114	Adventure
			127	Adventure
			128	Adventure
			135	Adventure
			136	Adventure

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:

Action (15)
Adventure (6)

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?

Regards

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

ID Name
3 Action
4 Action
5 Action
6 Adventure
7 Adventure
8 Adventure

Correct?

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
  end if

  if rs.Fields("Name")="Adventure" then
    adv = adv + 1
  end if

rs.MoveNext
loop
%>

Action = <%=act%>
Adventure = <%=adv%>

<% rs.close %>

If you want to use PHP (I believe this is how to do it):


<?php
$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.

Happy coding.

3

Nevermind, I worked it out, forgot about DISTINCT !

4

Still learning. :frowning:

Never knew you could do that. Should come in handy!

5

Must have just missed your reply. :stuck_out_tongue:

6

separate queries? no, there’s a much easier way :slight_smile:


SELECT Name
     , COUNT(*) AS ids
  FROM daTable
GROUP
    BY Name

luverly, innit :cool: