Counting records

[wikit]

I have a large database of information of characters
This contains "owner", "charactername" & a lot more info
the owner can easily repeat as they own more then one character.

what I'm trying to attempt to figure out how to do is create a list of UNIQUE names and how many times they are listed to own characters

example

NAME | NUMBER OWNED
jane | 5
john | 21
michael | 15

etc.

I have a script that queries the database and echos a list but I am unsure how to make that echo query the distinct 'owner' to count how many they have.

any help to point to an example how to do this would be greatly appreciated.

Something like this?

select name, count(number_owned) total_owned
from some_table s
group by name

[wikit]

dunno how to reply to a specific post >.<

Ah, ok. Perhaps something like:

echo "<TABLE>";

foreach($rows as $row)
{
    echo "<TR>" . PHP_EOL;
    echo "<TD>{$row['NAME']}<TD>" . PHP_EOL;
    echo "<TD>{$row['TOTAL_OWNED']}<TD>" . PHP_EOL;
    echo "</TR>" . PHP_EOL;
}

echo "</TABLE>";

It'll loop through the results and echo a row in a table (HTML) for each.

[wikit]

that makes sense (the html), if the column which lists the owner is called "Owner" how exactly does it count the number of times the owner's name appears for the 'Total owned' in this line? And does this do Distinct only?

select name, count(number_owned) total_owned

also
sry for slow response, daniweb logged me out and forced me to register with something else in order to log back in.... kinda frustrating....

[t_thakar]

this query below will only show names which comes more than once..
Instead of outputting all the name with number next to it..

hope this helps

SELECT name, count(number_owned) 
FROM some_table s
GROUP BY name
HAVING count(number_owned) > 1

If you could paste a sample of your table here (a handful of records) and show which columns and aggregate values (e.g. count) you want. I found the descriptions rather confusing

[wikit]

@diafol

A Snippet of the table

CREATE TABLE `owned` (
  `IDNumber` int(5) NOT NULL AUTO_INCREMENT,
  `Character` text NOT NULL,
  `Owner` text NOT NULL,
  `Gender` text,
  `Generation` text NOT NULL,
  PRIMARY KEY (`IDNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=1264 DEFAULT CHARSET=utf8;

INSERT INTO `owned` VALUES ('1','Estralita','Name1','Female','1st'), ('3','Zephyros','Name2','Male','2nd'), ('4','Kymo','Name3','Female','1st'), ('5','Seraphim','Name5','Male','1st'), ('6','Kieran','Name4','Male','1st'), ('7','Ryker','Name2','Male','1st'), ('8','Amapola','Name4','Female','1st'), ('9','Kika','Name8','Female','2nd'), ('12','Elizabeth','Name4','Female','1st'), ('13','Kelebek','Name4','Female','1st'), ('14','Aysu','Name6','Female','1st');

Another site I know of has the exact thing I'm trying to do.
http://mato.pe/gaians
Only thing is I do not want is the 'colorist' column

This is the best example I can give.

OK, this looks like you need to split your table. It seems you should have an owners' table, and then a owner_character table, linked by the owner_id. I'm assuming that the gender and generation pertain to the character not the owner, so...

owners

id - int/PK
name - varchar

owners_characters

id - int/PK
character - varchar
gender - enum - M/F
generation - tinyint
owner_id - int (FK)

So you'd get something like this:

SELECT o.name, COUNT(oc.id) AS cnt FROM owners AS o INNER JOIN owner_characters AS oc ON o.id = oc.owner_id GROUP BY o.id

You'd then pull data as $row['owner_name'] and $row['cnt'] in your loop.

You can try this yourself:

CREATE TABLE `owners` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into `owners` (`id`, `name`) values('1','Ben');
insert into `owners` (`id`, `name`) values('2','Dave');
insert into `owners` (`id`, `name`) values('3','Mike');

=======

CREATE TABLE `owner_characters` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `character` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` enum('M','F') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `generation` tinyint(1) DEFAULT NULL,
  `owner_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into `owner_characters` (`id`, `character`, `gender`, `generation`, `owner_id`) values('1','Xray','M','1','1');
insert into `owner_characters` (`id`, `character`, `gender`, `generation`, `owner_id`) values('2','Diafol','F','1','2');
insert into `owner_characters` (`id`, `character`, `gender`, `generation`, `owner_id`) values('3','Minatour','M','2','2');
insert into `owner_characters` (`id`, `character`, `gender`, `generation`, `owner_id`) values('4','Emrys','M','2','1');
insert into `owner_characters` (`id`, `character`, `gender`, `generation`, `owner_id`) values('5','Merlin','F','2','2');
insert into `owner_characters` (`id`, `character`, `gender`, `generation`, `owner_id`) values('6','Pegasus','M','1','3');

This SQL query gave the result (as expected):

name       cnt  
------  --------
Ben            2
Dave           3
Mike           1

[wikit]

@diafol
:S I'm kinda trying to avoid that simply for the fact the character table is already populated and is over 2000 records long.

is there any way i could do this from one table do you think?

Of course you can do that from a single table:

SELECT `owner`, COUNT(`IDNumber`) AS cnt FROM `owned` GROUP BY `owner`

However, this is not the recommended way to do it. Your table needs to be normalized, as you're supplying duplicate data (owner column) which may cause errors, depending on how you insert/update the data.

[Purvi_1]

Try this query :

select name,count(name) as Total from Table_name group by name