How do I calculate the revenue earned per person and per year-CodePudding

I suppose it is quite simple, I just can't get the hang of it. At the moment I have the following code:

SELECT s.first_name, s.last_name, s.staff_id, SUM(p.amount) AS 'Revenue'
  FROM payment p
  JOIN staff s
    ON s.staff_id = p.staff_id
 GROUP BY s.staff_id

This gives me the 2 staff members and their revenue but I'm still missing the yearly part.

I'm yet again using the sakila database, if anybody could help me I would really appreciate it, thanks in regards

Edit for the tables:

-- sakila.staff definition

CREATE TABLE `staff` (
  `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `address_id` smallint(5) unsigned NOT NULL,
  `picture` blob DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `store_id` tinyint(3) unsigned NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `username` varchar(16) NOT NULL,
  `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`staff_id`),
  KEY `idx_fk_store_id` (`store_id`),
  KEY `idx_fk_address_id` (`address_id`),
  CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `payment` (
  `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint(5) unsigned NOT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `rental_id` int(11) DEFAULT NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`),
  CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

CodePudding user response：

This can be done by simply adding the year to the GROUP BY clause, like this:

SELECT s.first_name, s.last_name, s.staff_id, SUM(p.amount) AS 'Revenue'
     , YEAR(payment_date) AS year
  FROM payment p
  JOIN staff s
    ON s.staff_id = p.staff_id
 GROUP BY s.staff_id, year
;

This means, generate a SUM for each group associated with rows having the same (staff_id, year) pairs.

The result:

 ------------ ----------- ---------- ---------- ------ 
| first_name | last_name | staff_id | Revenue  | year |
 ------------ ----------- ---------- ---------- ------ 
| Mike       | Hillyer   |        1 | 33255.38 | 2005 |
| Mike       | Hillyer   |        1 |   234.09 | 2006 |
| Jon        | Stephens  |        2 | 33646.95 | 2005 |
| Jon        | Stephens  |        2 |   280.09 | 2006 |
 ------------ ----------- ---------- ---------- ------