Hi,

I have a table with player names, scores, maps, and groups. A player can
optionally belong to a group and one or more players can belong to the
same group. A player can belog to a one or more groups, it is variable
on a per-map basis.

I'd like to find out how many groups there are and how many players
submitted a map score in that group.

I wrote the following query:

SELECT scoresuncharted.group,
COUNT(scoresuncharted.user) AS players
FROM scoresuncharted
GROUP BY scoresuncharted.group
HAVING (Not scoresuncharted.group='')
ORDER BY players DESC;

Unfortunatley, this isn't giving me exactly what I want. I get a list of
the groups, but I get the total number of maps played in each group.

Adding DISTINCT to the query did not change the results in any way.

I'm thinking I might have to add a subquery to the COUNT statement, but
I'm not familiar enough with SQL to figure out how to do it and al my
attempts get syntax errors.

I've been trying to read the MySQL manual at de.mysql.com, but it
doesn't help much if one can't figure it out to start with.

If I run this SQL query, I get a list of all the groups and users.

select Distinct scoresuncharted.user, scoresuncharted.group
from scoresuncharted
having (not scoresuncharted.group='')
order by scoresuncharted.group

But I just can't make it work to squeeze that into the other query as a
subquery.

All help appreciated.
Mike

On 2010-10-22 20:30, MikeB wrote:
[...]

Can you post the table definition (as a create statement) and some
sample data as insert statements?

Lennart Jonsson wrote:
here. I can post more data if needed.

CREATE TABLE IF NOT EXISTS `scoresUncharted` (
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
`map` varchar(32) NOT NULL,
`user` varchar(15) NOT NULL,
`group` varchar(20) NOT NULL,
`score` mediumint(8) unsigned NOT NULL,
`playCount` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`map`,`user`),
KEY `ts` (`ts`),
KEY `map` (`map`),
KEY `user` (`user`),
KEY `group` (`group`),
KEY `score` (`score`),
KEY `playCount` (`playCount`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `scoresUncharted`
--

INSERT INTO `scoresUncharted` (`ts`, `map`, `user`, `group`, `score`,
`playCount`) VALUES
('2010-08-14 11:39:14', '733997', 'Karsten75', 'Marauders', 8243, 1),
('2010-08-22 09:08:13', '734370', 'Sauffaus3', 'Resubmitted', 7619, 1),
('2010-08-14 12:07:57', '730003', 'Karsten75', 'Marauders', 8647, 1),
('2010-08-14 16:26:58', '714566', 'Karsten75', 'Marauders', 9240, 1),
('2010-08-15 00:59:46', '733996', 'Karsten75', 'Marauders', 8139, 1),
('2010-08-15 11:02:53', '0', 'Karsten75', '', 9295, 3),
('2010-08-16 02:34:01', '733999', 'Karsten75', '', 9111, 1),
('2010-08-16 02:52:38', '733998', 'Karsten75', '', 8251, 1),
('2010-08-16 13:46:08', '730370', 'Karsten75', '', 8452, 1),
('2010-08-17 16:56:27', '734000', 'Karsten75', '', 8744, 1),
('2010-08-18 01:13:34', '368757', 'Karsten75', '', 8440, 1),
('2010-08-18 01:54:22', '405282', 'Karsten75', '', 7139, 1),
('2010-08-19 15:37:58', '734001', 'Karsten75', '', 8579, 1),
('2010-08-19 15:57:55', '734002', 'Karsten75', '', 7746, 1),
('2010-08-19 16:19:40', '734004', 'Karsten75', '', 7964, 1),
('2010-08-22 09:07:27', '734370', 'Sauffaus2', '', 7619, 1);

On 2010-10-23 14:48, MikeB wrote:
Given your data (I replace a number of reserved words in the table) we have:

SELECT x.xgroup, COUNT(x.xuser) AS players FROM
scoresUncharted x GROUP BY x.xgroup HAVING (x.xgroup <> '')
ORDER BY players DESC;
+-------------+---------+
2 rows in set (0.05 sec)

If you want the distinct number of players in each group you can:

SELECT x.xgroup, COUNT(distinct x.xuser) AS players FROM
scoresUncharted x GROUP BY x.xgroup HAVING (x.xgroup <> '')
ORDER BY players DESC;
+-------------+---------+
2 rows in set (0.00 sec)

Is this what you want?


/Lennart

In article <4cc2d98a (AT) news (DOT) x-privat.org>, MikeB <mpbrede (AT) gmail (DOT) com> wrote:
[...]
Just FYI, since 'map' is already the high-order portion of the primary key,
having a separate index on that column alone is completely unnecessary.

Lennart Jonsson wrote:
Works great, thanks!

I do know that those field names are reserved, but I have to work with
what I've been given, so I couldn't change them. I don't own the database.

Thanks again.

MikeB

Doug Miller wrote:
Yes, I figured that, but the person who created this defined it this way
- I just got a dump from the database to work with.