MySQL Combining two queries with same structure and add column

Question

1. I have one query to get a sum of how many users per `worksite` that appears on each date.

2. I have another query that gets how many documents were made on each date by worksite

Since both tables are in the same format, is it possible to take #1's value for each worksite and date and #2's value for each worksite and date, and join them so they appear in the same table on just another column?

So that it looks like this:

| Worksite   | Date       | Users On Site | Completed |
| ---------- | ---------- | -----------   | --------- |
| worksite_1 | 2019-01-01 | 2             | 2
| worksite_2 | 2019-01-01 | 0             | 2
| worksite_1 | 2019-01-02 | 1             | 1
| worksite_2 | 2019-01-02 | 0             | 2 
| worksite_1 | 2019-01-03 | 2             | 2            
| worksite_1 | 2019-01-04 | 1             | 1             
| worksite_2 | 2019-01-04 | 0             | 1            
| worksite_1 | 2019-01-05 | 1             | 1             
| worksite_2 | 2019-01-05 | 0             | 1            
| worksite_1 | 2019-01-06 | 1             | 1            
| worksite_2 | 2019-01-06 | 0             | 1

#1

Schema (MySQL v5.7)

CREATE TABLE OnSite
    (`uid` varchar(55), `worksite_id`  varchar(55), `timestamp` datetime)
;

INSERT INTO OnSite
    (`uid`, `worksite_id`, `timestamp`)
VALUES
  ("u12345", "worksite_1", '2019-01-01'),
  ("u12345", "worksite_1", '2019-01-02'),
  ("u12345", "worksite_1", '2019-01-03'),
  ("u12345", "worksite_1", '2019-01-04'),
  ("u12345", "worksite_1", '2019-01-05'),
  ("u12345", "worksite_1", '2019-01-06'),
  ("u1", "worksite_1", '2019-01-01'),
  ("u1", "worksite_1", '2019-01-02'),
  ("u1", "worksite_1", '2019-01-05'),
  ("u1", "worksite_1", '2019-01-06')

;

Query #1

SELECT    worksite_id, DATE(timestamp) Date, COUNT(DISTINCT uid) `Users On Site`
FROM      OnSite
GROUP BY  DATE(timestamp), worksite_id;

| worksite_id | Date       | Users On Site |
| ----------- | ---------- | ------------- |
| worksite_1  | 2019-01-01 | 2             |
| worksite_1  | 2019-01-02 | 2             |
| worksite_1  | 2019-01-03 | 1             |
| worksite_1  | 2019-01-04 | 1             |
| worksite_1  | 2019-01-05 | 2             |
| worksite_1  | 2019-01-06 | 2             |

View on DB Fiddle

#2

Schema (MySQL v5.7)

CREATE TABLE Documents
    (`document_id` varchar(55), `uid` varchar(55), `worksite_id`  varchar(55), `type` varchar(55), `timestamp` datetime)
;

INSERT INTO Documents
    (`document_id`, `uid`, `worksite_id`, `type`, `timestamp`)
    
VALUES
  ("1",     "u12345",   "worksite_1", 'work_permit',    '2019-01-01 00:00:00'),
  ("2",     "u12345",   "worksite_2", 'job',            '2019-01-01 00:00:00'),
  ("3",     "u12345",   "worksite_1", 'work_permit',    '2019-01-02 00:00:00'),
  ("4",     "u12345",   "worksite_2", 'job',            '2019-01-02 00:00:00'),
  ("5",     "u12345",   "worksite_1", 'work_permit',    '2019-01-03 00:00:00'),
  ("6",     "u12345",   "worksite_2", 'job',            '2019-01-04 00:00:00'),
  ("7",     "u12345",   "worksite_1", 'work_permit',    '2019-01-04 00:00:00'),
  ("8",     "u12345",   "worksite_2", 'work_permit',    '2019-01-05 00:00:00'),
  ("9",     "u12345",   "worksite_1", 'job',            '2019-01-05 00:00:00'),
  ("10",    "u12345",   "worksite_2", 'work_permit',    '2019-01-06 00:00:00'),
  ("11",    "u12345",   "worksite_1", 'work_permit',    '2019-01-06 00:00:00'),
  ("12",    "u12345",   "worksite_2", 'work_permit',    '2019-01-01 00:00:00'),
  ("13",    "u12345",   "worksite_1", 'job',            '2019-01-01 00:00:00'),
  ("14",    "u12345",   "worksite_2", 'work_permit',    '2019-01-02 00:00:00'),
  ("15",    "u12345",   "worksite_1", 'work_permit',    '2019-01-03 00:00:00')

;

Query #2

SELECT    worksite_id 'Worksite', 
          Date(timestamp) Date,
          COUNT(worksite_id) `Completed`
FROM      Documents
GROUP BY  Date(timestamp), worksite_id;

| Worksite   | Date       | Completed |
| ---------- | ---------- | --------- |
| worksite_1 | 2019-01-01 | 2         |
| worksite_2 | 2019-01-01 | 2         |
| worksite_1 | 2019-01-02 | 1         |
| worksite_2 | 2019-01-02 | 2         |
| worksite_1 | 2019-01-03 | 2         |
| worksite_1 | 2019-01-04 | 1         |
| worksite_2 | 2019-01-04 | 1         |
| worksite_1 | 2019-01-05 | 1         |
| worksite_2 | 2019-01-05 | 1         |
| worksite_1 | 2019-01-06 | 1         |
| worksite_2 | 2019-01-06 | 1         |

View on DB Fiddle

Can you update the question and use the correct Completed values in the expected results based on the example data.. So we can verify the correctness of the query we possible write? — Raymond Nijland
– Raymond Nijland, Commented Jan 16, 2019 at 19:23

Maksym Fedorov · Accepted Answer · 2019-01-16 19:39:24Z

1

You can use JOIN. For example:

SELECT
  IFNULL(OnSite.worksite_id, Documents.worksite_id) as `Worksite`,
  DATE(IFNULL(OnSite.timestamp, Documents.timestamp)) as `Date`,
  COUNT(DISTINCT OnSite.uid) as `Users_On_Site`,
  COUNT(Documents.worksite_id) as `Completed`
FROM OnSite
  LEFT JOIN Documents ON OnSite.worksite_id = Documents.worksite_id AND DATE(OnSite.timestamp) = DATE(Documents.timestamp)
GROUP BY `Date`, `Worksite`;

edited Jan 16, 2019 at 19:39

answered Jan 16, 2019 at 19:28

Maksym Fedorov

6,4462 gold badges13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Raymond Nijland Over a year ago

"You can use LEFT JOIN" You know you used a INNER JOIN in the SQL right?

bryan Over a year ago

I'm getting an error when I try this. Not quite sure how it works: > Query Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uid) as Users_On_Site, COUNT(Documents.worksite_id) as Completed FROM OnS' at line 3

Raymond Nijland Over a year ago

COUNT(OnSite.DISTINCT uid) should be COUNT(DISTINCT OnSite.uid) @bryan also COUNT(Documents.worksite_id) as Completed might also require a DISTINCT` if you notice values which are double which you expected.

Raymond Nijland Over a year ago

Your SQLfiddle link uses other dates then your example data here @bryan

Raymond Nijland Over a year ago

@bryan you need to repeat COUNT(Documents.worksite_id) / COUNT(DISTINCT OnSite.uid) as total of use a subquery SELECT alias.*, (Completed / Users_On_Site) AS total FROM (SELECT IFNULL(OnSite.worksite_id, Documents.worksite_id) as Worksite... ) AS alias... which is required in every SQL dialect.

|

Collectives™ on Stack Overflow

MySQL Combining two queries with same structure and add column

1. I have one query to get a sum of how many users per `worksite` that appears on each date.

2. I have another query that gets how many documents were made on each date by worksite

#1

#2

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1. I have one query to get a sum of how many users per worksite that appears on each date.

2. I have another query that gets how many documents were made on each date by worksite

#1

#2

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related

1. I have one query to get a sum of how many users per `worksite` that appears on each date.