Home / Questions / 474955
Accepted
Revolucion for Monica
Asked: 2022-08-05 08:03:21 +0800 CST 2022-08-05 08:03:21 +0800 CST

The sum of the columns of a schema does not give the same result twice

  • 772

I made a table sharesin which I inserted the sum of the columns of another table communities:

CREATE TABLE communities
    (`community_id` int, `shares_copylink` int, `shares_email` int, `shares_facebook` int, `shares_messenger` int, `shares_pinterest` int, `shares_twitter` int, `shares_whatsapp` int)
;
    
INSERT INTO communities
    (`community_id`, `shares_copylink`, `shares_email`, `shares_facebook`, `shares_messenger`, `shares_pinterest`, `shares_twitter`, `shares_whatsapp`)
VALUES
    (1, 0, 0, 0, 0, 0, 0, 0),
    (2, 2, 2, 196, 0, 0, 0, 0),
    (3, 8, 20, 0, 8, 0, 0, 12),
    (6, 0, 0, 0, 0, 0, 0, 0),
    (7, 0, 0, 32, 0, 0, 0, 0),
    (8, 2, 2, 0, 1, 0, 0, 0),
    (9, 0, 0, 0, 0, 0, 0, 0),
    (12, 0, 0, 1, 1, 0, 0, 1),
    (14, 1, 1, 11, 0, 0, 0, 3),
    (16, 0, 0, 0, 0, 0, 0, 0),
    (59, 0, 0, 0, 0, 0, 0, 0),
    (60, 0, 0, 0, 0, 0, 0, 0),
    (61, 0, 0, 0, 0, 0, 0, 0),
    (62, 2, 3, 28, 0, 0, 0, 9),
    (65, 0, 0, 0, 0, 0, 0, 0),
    (66, 0, 0, 0, 0, 0, 0, 0),
    (67, 0, 0, 0, 0, 0, 0, 0),
    (68, 0, 0, 0, 0, 0, 0, 0),
    (69, 0, 0, 0, 0, 0, 0, 0),
    (70, 0, 0, 0, 0, 0, 0, 0),
    (71, 0, 0, 0, 0, 0, 0, 0),
    (72, 0, 0, 0, 0, 0, 0, 0),
    (73, 0, 0, 0, 0, 0, 0, 0),
    (74, 0, 0, 0, 0, 0, 0, 0),
    (75, 0, 0, 0, 0, 0, 0, 0),
    (76, 0, 0, 0, 0, 0, 0, 0),
    (77, 0, 0, 0, 0, 0, 0, 0),
    (78, 0, 0, 0, 0, 0, 0, 0),
    (79, 0, 0, 0, 0, 0, 0, 0),
    (80, 0, 0, 0, 0, 0, 0, 0),
    (81, 0, 0, 0, 0, 0, 0, 0),
    (82, 0, 0, 0, 0, 0, 0, 0),
    (83, 0, 0, 0, 0, 0, 0, 0),
    (84, 0, 0, 0, 0, 0, 0, 0),
    (85, 0, 0, 0, 0, 0, 0, 0),
    (86, 0, 0, 0, 0, 0, 0, 0),
    (87, 0, 0, 0, 0, 0, 0, 0),
    (88, 0, 0, 0, 0, 0, 0, 0),
    (89, 0, 0, 0, 0, 0, 0, 0),
    (90, 0, 0, 0, 0, 0, 0, 0),
    (91, 0, 0, 0, 0, 0, 0, 0),
    (93, 0, 0, 0, 0, 0, 0, 0),
    (94, 0, 0, 0, 0, 0, 0, 0),
    (95, 0, 0, 0, 0, 0, 0, 0),
    (96, 0, 0, 0, 0, 0, 0, 0),
    (97, 0, 0, 0, 0, 0, 0, 0),
    (98, 0, 0, 0, 0, 0, 0, 0),
    (99, 0, 0, 0, 0, 0, 0, 0),
    (100, 0, 0, 0, 0, 0, 0, 0),
    (101, 0, 0, 0, 0, 0, 0, 0),
    (102, 0, 0, 0, 0, 0, 0, 0),
    (103, 0, 0, 0, 0, 0, 0, 0),
    (104, 0, 0, 2, 0, 0, 0, 4),
    (105, 0, 0, 0, 0, 0, 0, 0),
    (106, 0, 0, 0, 0, 0, 0, 0),
    (107, 0, 0, 0, 0, 0, 0, 0),
    (108, 0, 0, 0, 0, 0, 0, 0),
    (109, 0, 0, 1, 0, 0, 0, 0),
    (110, 0, 0, 0, 0, 0, 0, 0),
    (111, 0, 0, 0, 0, 0, 0, 0),
    (142, 0, 0, 0, 0, 0, 0, 0),
    (143, 0, 0, 0, 0, 0, 0, 0),
    (145, 0, 1, 39, 1, 0, 0, 4),
    (146, 0, 0, 0, 0, 0, 0, 0),
    (147, 0, 0, 0, 0, 0, 0, 0),
    (148, 0, 1, 1, 1, 0, 0, 0)
;

CREATE TABLE shares (Date date, Shares int);

INSERT INTO
`shares` (`Date`, `Shares`)
SELECT
  CURDATE() as date,
  SUM(shares_copylink) + SUM(shares_email) + SUM(shares_facebook) + SUM(shares_messenger) + SUM(shares_pinterest) + SUM(shares_twitter) + SUM(shares_whatsapp) AS total
  FROM communities

On the same day I added these columns minus this result I just inserted:

SELECT CURDATE(), (SUM(shares_copylink) + SUM(shares_email) + SUM(shares_facebook) + SUM(shares_messenger) + SUM(shares_pinterest) + SUM(shares_twitter) + SUM(shares_whatsapp) - SUM(`Shares`)) AS daily_shares FROM communities, shares

And it gives me a result other than 0 which is strange.

http://sqlfiddle.com/#!9/001e83/34/0

I use MySQL 5.7

mysql

1 Answers

  1. Best Answer

    We add a line with the sum of the shares of the first table minus the result of the last line

        c.execute("""
    INSERT INTO
    shares (`Date`, `Shares`)
    SELECT CURDATE(), 
        (
          (
          SELECT 
          (SUM(shares_copylink) 
          + SUM(shares_email)
          + SUM(shares_facebook) 
          + SUM(shares_messenger)
          + SUM(shares_pinterest) 
          + SUM(shares_twitter) 
          + SUM(shares_whatsapp)) as sumval
          FROM communities
          ) - SUM(`Shares`)
        ) AS daily_shares
        FROM shares 
        WHERE EXISTS (SELECT * FROM shares);
    """)
    • 0