I am trying to create a report and count documents / projects within a given time range. I think I am doing something wrong with the where clause.

What I have:

• A project table with project ids
• Different document tables each with an document id, project id, and a document creation date:

projecttable

document type A table

document type B table

Expected result:

Given Time Range from 2022-01 to 2022-03

I tried the following select:

    SELECT "project"."id",
       COUNT(DISTINCT(documenta.id)) AS documentA_total
       COUNT(DISTINCT(documentb.id)) AS documentB_total,       
FROM project
LEFT JOIN documenta ON documenta.projectid = project.id
LEFT JOIN documentb ON documentb.projectid = project.id
WHERE 
("documenta"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}')
OR 
("documentb"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}')
GROUP BY project.id;

I think the problem is at the where clause. Because I am getting the wrong countings.

CodePudding user response：

You need to update your join condition to include the date filter and remove them from where clause. I have renamed the table and column names for simplicity. Here is sqlfiddle

 SELECT p.id,
   COUNT(DISTINCT(a.id)) AS documentA_total,
   COUNT(DISTINCT(b.id)) AS documentB_total      
FROM project p
LEFT JOIN docA a ON p.id = a.project_id and a.created_at BETWEEN '2022-01-01' AND '2022-03-31'
LEFT JOIN docB b ON p.id = b.project_id and b.created_at BETWEEN '2022-01-01' AND '2022-03-31'
GROUP BY p.id;

I have renamed the table and column names for simplicity. Your exact query will be similar to below query,

SELECT "project"."id",
       COUNT(DISTINCT(documenta.id)) AS documentA_total
       COUNT(DISTINCT(documentb.id)) AS documentB_total,       
FROM project
LEFT JOIN documenta ON documenta.projectid = project.id and "documenta"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}'
LEFT JOIN documentb ON documentb.projectid = project.id and "documentb"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}'
GROUP BY project.id;

CodePudding user response：

Your code is no mysql code as it would not allow a column name with double quotes

But still, you must count the data before joining

SELECT "project"."id",
       COALESCE(documentA_total,0) AS documentA_total,
       COALESCE(documentB_total,0) AS documentB_total
FROM project
LEFT JOIN (SELECT Count(*) as documentA_total, projectid FROM documenta WHERE 
("documenta"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}') GROUP BY projectid) as doca ON doca.projectid = project.id
LEFT JOIN (SELECT Count(*) as documentB_total, projectid FROM documentb WHERE ("documentb"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}')
GROUP BY projectid) as docb ON docb.projectid = project.id