I have an SQL statement that gives a list of creditors on jobs. In the example in the image both of creditors belong to the same job. So on the last row that is displaying the TOTALS the number of jobs is 1 which is correct because it is the same job. Also the columns that say NotPOD is also correct saying just 1. But for all the other columns it is adding the totals. Like the Column that says FL (it is a piece type). There is only 1 FL piece type on this job. But since the job has two creditors the total is displaying two even though only one exists.
I don't know how the totals are correct for jobs and NotPOD.
Here is the sql statement:
SET group_concat_max_len=10000000; set @sql = null; select group_concat(distinct concat( 'sum((select sum(jp1.AdvisedQty) from job_pieces jp1 left join piecestype pt1 on jp1.TypeID = pt1.ID where jp1.jobid=jn.id and pt1.code=''', pt.Code , ''' )) AS `', pt.Code, '`' ) ) into @sql FROM creditor c LEFT JOIN invoicedetail i on c.ID = i.creditorId LEFT JOIN job_new jn ON i.JobID = jn.ID LEFT JOIN job_pieces jp ON jp.JobID = jn.ID LEFT JOIN piecestype pt on jp.TypeID = pt.ID WHERE c.Company_ID = ?compid; set @sql = concat('select ifnull(c.Name,''TOTAL'') as Name, COUNT(distinct jn.ID) as Jobs, sum(case when (select count(jbs.status) from jobstat jbs where jbs.jobid=jn.id and jbs.Status=''DEL'')>0 then 1 else 0 end) as Delivered, sum(case when (select count(jbs.status) from jobstat jbs where jbs.jobid=jn.id and jbs.Status=''POD'')>0 then 1 else 0 end) as POD, SUM((select COUNT(ID) from job_debriefs WHERE JobID = jn.ID)) as Debriefs, sum(jn.OutTurn) as Outturn, SUM(jn.ActualWeight) as GrossWt, ', @sql, ' FROM creditor c LEFT JOIN invoicedetail i on c.ID = i.creditorId LEFT JOIN job_new jn ON i.JobID = jn.ID LEFT JOIN job_pieces jp ON jp.JobID = jn.ID LEFT JOIN piecestype pt on jp.TypeID = pt.ID WHERE c.Company_ID = ', ?compid, GROUP BY c.Name WITH ROLLUP 2 Answers
Answers 1
The with rollup-flag will do different things when you use count(distinct) and sum.
For COUNT(distinct jn.ID) it will give you the number of distinct Jobids over all the displayed data. That is probably what you want.
For a sum-column like sum(case...) as POD it will just give you the sum over all displayed values - and POD would be "2", if the job had the status "POD".
As for the NotPOD-Column: I assume you just calcalute it in your client (maybe as Jobs - POD), and since POD was 0 (could have been 2 though), it looks fine. I assume you calculate "Units" in your client too (as the sum of the 4 piece types). If not, your code doesn't fit to your picture.
So how to fix it?
Well, basically you just can't use with rollup, mainly because you don't want to have the sums of the displayed values, and that is what with rollup is for.
The easiest solution is to calculate the total-row manually and merge them with union, e.g. something like this:
select c.Name as Name, COUNT(distinct jn.ID) as Jobs, ... sum(jn.OutTurn) as Outturn, SUM(jn.ActualWeight) as GrossWt, ... FROM creditor c LEFT JOIN invoicedetail i on c.ID = i.creditorId LEFT JOIN job_new jn ON i.JobID = jn.ID -- !!! DO NOT USE LEFT JOIN job_pieces jp ON jp.JobID = jn.ID !!! -- !!! DO NOT USE LEFT JOIN piecestype pt on jp.TypeID = pt.ID !!! WHERE c.Company_ID = ', ?compid, GROUP BY c.Name, ' UNION ''Total'', COUNT(distinct jn.ID) as Jobs, ... sum(jn.OutTurn) as Outturn, SUM(jn.ActualWeight) as GrossWt, ... -- you have to figure out what number you need here for the pieces -- you probably want to seperate that part too (as you did with @sql) -- (select sum(jp1.AdvisedQty) from job_pieces jp1 ... ) as FL ... from job_new jn WHERE exists(select 1 FROM creditor c JOIN invoicedetail i on c.ID = i.creditorId where i.JobID = jn.ID and c.Company_ID = ', ?compid, ' ) No group by in the second part! You have to figure out what values you want for the per-pieces-sums and you probably want to prepare them with a statement similarly to your select group_concat(distinct ...
And a warning: if i understand your datamodel correctly, you absolutely have to remove the
LEFT JOIN job_pieces jp ON jp.JobID = jn.ID LEFT JOIN piecestype pt on jp.TypeID = pt.ID statements, because they will ruin your values when you have more then one jobpiece or piecetype in one job (you can try adding a 2nd value to a job and see that). And if it is possible that you have the same job twice on one invoicedetail, you have to fix that part too.
And beware that in the union-part, you cannot join over the customers (you would count everything several times).
Answers 2
I suspect that your problem is because of your group by condition.
you perform GROUP BY c.Name WITH ROLLUP and as far as I understand - your c.Name is the name of the creditor.
Try grouping by job id or other unique identifier of your record.
0 comments:
Post a Comment