CODEWITHSUNDEEP
sqlsumdistinct
Water
Water

Reputation: 3715

SQL sum and average query with

I'm trying to figure out how to do some more advanced SQL features (advanced for me at least).

I would like to take the following data and:

This is a quick example data sheet I made:

Table: tblPoints

ID   NAME   ROUND  POINTS
---------------------------
1    Dummy    1      30
2    Yes      1      45
3    Hi       1      20
4    Dummy    2      45
5    Yes      2      15
6    Hi       2      65
7    Newbie   2      10
8    Dummy    3      150
9    Yes      3      120
10   Hi       3      145

This is what I'd like to see (notice again it's just for rounds 1 and 2):

NAME   SUM_POINTS_EVERYONE  SUM_POINTS   COUNT_NAMES
------------------------------------------------------
Dummy        230               75            4
Yes          230               60            4
Hi           230               85            4
Newbie       230               10            4

I started with this to select only the rounds I want and get 2 of the new columns:

SELECT name,
       (SELECT SUM(points) FROM tblPoints WHERE round IN (1, 2)) AS sum_points_everyone,
       (SELECT COUNT(DISTINCT name) FROM tblPoints WHERE ROUND IN (1, 2)) AS count_names
FROM tblPoints
WHERE round IN (1, 2);

The formula for the custom rows are as follows:

Sum points = Sums up the points for the name of the player for rounds 1 and 2 (in this case)

Sum points everyone = Sums up everyones points from rounds 1 and 2 (in this case)

Count names = The COUNT() of all the unique names

As you can see, I can get the rows SUM_POINTS_EVERYONE and COUNT_NAMES, but I'm stuck from here on how to get the other ones. How would I output only rows for distinct names with the above fields?

NOTE: If this is something that is beyond the scope of SQL, I can manually do it with PHP -- but I'm trying to see if I can just do it in one query so I don't have to shift work over to PHP.

If anyone wanted to quickly make the table, here's the dump:

CREATE DATABASE IF NOT EXISTS `testdb`;
USE `testdb`;
CREATE TABLE `tblPoints` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `round` int(10) unsigned NOT NULL,
  `points` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `tblPoints` VALUES (1,'Dummy',1,30),(2,'Yes',1,45),(3,'Hi',1,20),(4,'Dummy',2,45),(5,'Yes',2,15),(6,'Hi',2,65),(7,'Newbie',2,10),(8,'Dummy',3,50),(9,'Yes',3,20),(10,'Hi',3,45);

Upvotes: 1

Views: 129

Answers (1)

Hanky Panky
Hanky Panky

Reputation: 46900

SELECT NAME,
(SELECT SUM(POINTS) FROM tblPoints WHERE ROUND IN (1,2)) AS SUM_POINTS_EVERYONE,
SUM(POINTS) AS SUM_POINTS,
COUNT(1) AS COUNT_NAMES 
FROM tblPoints 
WHERE ROUND IN (1,2)
GROUP BY NAME

Output

|   NAME | SUM_POINTS_EVERYONE | SUM_POINTS | COUNT_NAMES |
|--------|---------------------|------------|-------------|
|  Dummy |                 230 |         75 |           2 |
|     Hi |                 230 |         85 |           2 |
| Newbie |                 230 |         10 |           1 |
|    Yes |                 230 |         60 |           2 |

Fiddle

Edit for names count

SELECT NAME,
(SELECT SUM(POINTS) FROM tblPoints WHERE ROUND IN (1,2)) AS SUM_POINTS_EVERYONE,
SUM(POINTS) AS SUM_POINTS,
(SELECT COUNT(DISTINCT NAME) FROM tblPoints WHERE ROUND IN (1,2)) AS COUNT_NAMES
FROM tblPoints
WHERE ROUND IN (1,2)
GROUP BY NAME

Output

|   NAME | SUM_POINTS_EVERYONE | SUM_POINTS | COUNT_NAMES |
|--------|---------------------|------------|-------------|
|  Dummy |                 230 |         75 |           4 |
|     Hi |                 230 |         85 |           4 |
| Newbie |                 230 |         10 |           4 |
|    Yes |                 230 |         60 |           4 |

Fiddle

Upvotes: 1

Related Questions