Sum pivot query troubles (1 Viewer)

[adhoustonj]

Hello AWF,
I have a task table like below, and I am trying to write a query that returns the 2nd table below.
I need to sum up the task_secs by station and have a column for each model. The by model sum includes each task_secs where there is no model.

So for station 1 model 1 - it will sum the task ID's 1,2,3,4
for station 1 model 2 - it will sum task ID's 1,3,5

I'm scratching my head a bit while I thought this would be simpler.
I was using a pivot to sum it up for each model - null, 1,2,3 - but I then need to sum up null + model 1, null + model 2, null + model 3, etc.

There also can be any number of model variants in this table, so I liked that the pivot returned dynamic number of columns, but I am stuck.
If anyone could help that would be amazing.

Task_ID	Model	Station_ID	Task_secs
1		1	5
2	1	1	8
3		1	10
4	1	1	16
5	2	1	81
6	3	1	20
7	1	2	7
8		2	19
9	2	2	2

Station_ID	Model_1	Model_2	Model_3
1	39	96	35
2	26	21

 

[plog]

First, let's put aside the pivot and just work on getting the data you need in a query. Pivoting from there will be trivial. So instead of your second set of data, we need to work to achieve this:

Station_ID, Model, TaskSecs
1, 1, 39
1, 2, 96
1, 3, 35
2, 1, 26
2, 2, 21
2, 3, 20

Q1. Build a totals query to achieve the above results plus nulls. Group by Station_ID and Model, Sum Task_secs and call it Secs1. Use no criteria on this because we want it to have your null Models.

Q2. Use Q1 above as the source of a new totals query to sum your null models. It will show 2 fields--Station_ID (Group By) and Secs1 (Sum) call this Secs2 and use Model as criteira (IS Null).

Q3. Build another query using the above 2 queries. LEFT JOIN Q2 to Q1 (show all from Q1) and bring down Station_ID and Model from Q1 into the query. Do not make this a totals query. Use Is Not Null as criteria under Model and then build a new field like so:

TaskSecs: Secs1 + Secs2

Run it and the 3rd query will get the data you need. After that use it as the source of yet a new query where you pivot and get the final results you want.

 

[adhoustonj]

Big thanks for getting my gears spinning here Plog.

Am I following correclty?

SELECT a.station_id, a.model, a.tasksecs + b.sec2 as tasksecs

FROM(
SELECT station_id, model, sum(tasksecs) as tasksecs
FROM tblTasks
GROUP BY station_id, model
) as a
LEFT JOIN ( SELECT station_id, sum(tasksecs) as sec2
        FROM tblTasks
        WHERE model is null
        GROUP BY station_id
        ) as b
ON a.station_id = b.station_id
WHERE b.model is not null

 

[plog]

Yes that looks right.

 

[adhoustonj]

I am a lot closer here. I'm uploading a demo db with my queries.

Where I'm at with the pivot query after walking through the above queries outlined:
I may have used a bad example with not including a station with no model variant, but I do need to carry that over if the station has no model specific tasks.

pcd_id	stat_id	<>	2	18
131	14	74		91
131	16		138
131	18	113
131	21	88
131	22	115	165
131	23	24	114	118

Where I am trying to get to:

pcd_id	stat_id	2	18
131	14	74	91
131	16	138	0
131	18	113	113
131	21	88	88
131	22	165	115
131	23	114	118

 

[plog]

You've not eliminated null mdtl_id fields from qryComboSecs3.

 

[adhoustonj]

If i eliminate the nulls then I do not get all the stations. So I had the iif statement to not sum up the stations with no models returned from qry1 with the null model stations in qry2.

I am trying to carry the null value into the model columns - like stat_id 18 needs to have 113 returned for mdtl_id 2 and 18.

SELECT qryTaskSecs1.pcd_id, qryTaskSecs1.stat_id, qryTaskSecs1.mdtl_id, IIf([qryTaskSecs1].mdtl_id Is Null,Nz([qryTaskSecs1].secs1,0),Nz([qryTaskSecs1].secs1,0)+Nz([qryStatSecs2].secs2,0)) AS secs
FROM qryTaskSecs1 LEFT JOIN qryStatSecs2 ON (qryTaskSecs1.stat_id = qryStatSecs2.stat_id) AND (qryTaskSecs1.pcd_id = qryStatSecs2.pcd_id)
WHERE (((qryTaskSecs1.pcd_id)=131))

 

[plog]

If i eliminate the nulls then I do not get all the stations.

Where/how do you want the stations without models to appear? For example:

pcd_id = 130, stat_id = 4, mdtl_id = NULL

That's the only record in tblTask with stat_id = 4. In your pivotted query where/how would you like that data to appear? Right now the seconds are falling under the <> column.

 

[adhoustonj]

pcd_id 130 doesn't have any mdtl_id values to populate columns with, so unless I look at calling the null mdtl_id something like "standard" and then it would populate a "standard" model column with the value when I pivot. The <> column is fine for now.

Two other example:
pcd_id = 131, stat_id = 14 I am trying to get the following

mdtl_id 2 just sums up the task_secs for null mdtl_id or mdtl_id = 2
mdtl_id 18 sums up the task_secs for null mdtl_id or mdtl_id = 18

pcd_id	stat_id	2	18
131	14	74	91

pcd_id = 131, stat_id = 18
No mdtl_id for this station, it would just sum up the null

pcd_id	stat_id	2	18
131	18	113	113

 

[plog]

In tblTasks there are 10 records where pcd_id= 131 and stat_id = 14. Filter your table to find them.

0 have mdtl_id = 2 so you will never get results for that

8 have mdtl_id = NULL and if you add those task_secs together you get 74

2 have mdti_id = 18 and if you add those task_secs together you get 17

So, I don't know how you expect to get 113 for mdti_id = 18

Please walk me through your expectations for this starting at the table level like I did.

 

[adhoustonj]

Below are my comments:
In tblTasks there are 10 records where pcd_id= 131 and stat_id = 14.

0 have mdtl_id = 2 so you will never get results for that
* so I want it to sum all of the task_secs that are null, or have mdtl_id = 2, which there are 0. Return value 74

8 have mdtl_id = NULL and if you add those task_secs together you get 74
* yes

2 have mdti_id = 18 and if you add those task_secs together you get 17
* yes - but I need it to be the 2 have mdtl_id = 18 that is 17 + 74 from the 8 records that have mdtl_id = null

So, I don't know how you expect to get 113 for mdti_id = 18
* There are 4 records where pcd_id = 131 and stat_id = 18. All 4 records have mdtl_id = Null, so for any mdtl_id that are present for all stations on pcd_id = 131 which would be pivoted into columns, in this case mdtl_id's 2 and 18, then the value 113 would be reflected.

 

[plog]

* so I want it to sum all of the task_secs that are null, or have mdtl_id = 2, which there are 0. Return value 74

I don't understand how mdtl_id = Null get assigned to mdtl_ID = 2. Why not 4 or 17 or any other number that doesn't exist?

* yes - but I need it to be the 2 have mdtl_id = 18 that is 17 + 74 from the 8 records that have mdtl_id = null

And that equals 91 not 113. You expected 113 for mdtl_ID = 18 in your last example. Walk me through getting to 113.

* There are 4 records where pcd_id = 131 and stat_id = 18. All 4 records have mdtl_id = Null,

Now you've completely lost me. why did we shift to talking about an unrelated stat_id? We were trying to focus on stat_id = 14 and then you changed toa completely different set of records.

I might be to lost at this point to help.

 

[adhoustonj]

Sorry - There were two examples in post #9 - pcd_id = 131, stat_id = 18 that was where the 113 came from that you picked up on post #10.
In the 2nd example - that stat_id = 18 had all records with mdtl_id = Null. The query you originally suggested was working okay until there is a station with no mdtl_id values.

I want the mdtl_id = null to be summed up with any other mdtl_id associated with the station.
So if there are 5 tasks at 1 station, 3 mdtl_id = null, 1 mdtl = 2, and 1 mdtl = 18 I want the total time summed up for the 3 null + 1 mdtl = 2, and then summed up for the 3 null + mdtl = 18

 

[adhoustonj]

Got it!

Thanks for all the help @plog
Couldn't have done it without you.

TRANSFORM Sum(Nz(g.secs1,0)+Nz(h.secs2,0)) AS task_secs
SELECT g.stat_id, g.pcd_id
FROM (SELECT c.stat_id, c.mdtl_id, c.pcd_id, f.secs1
FROM
(
SELECT a.stat_id, b.mdtl_id, a.pcd_id
FROM
(SELECT DISTINCT stat_id, pcd_id FROM tblTask WHERE pcd_id = 131) as a,
(SELECT DISTINCT mdtl_id, pcd_id FROM tblTask WHERE pcd_id = 131) as b
) as c
LEFT JOIN (SELECT tblTask.pcd_id, tblTask.stat_id, tblTask.mdtl_id, sum(tblTask.task_secs) as secs1
                  FROM tblTask GROUP BY tblTask.pcd_id, tblTask.stat_id, tblTask.mdtl_id) as f on c.pcd_id = f.pcd_id AND c.stat_id = f.stat_id AND nz(c.mdtl_id,999999) = nz(f.mdtl_id,999999)
)  AS g LEFT JOIN (SELECT tblTask.pcd_id, tblTask.stat_id, sum(tblTask.task_secs) as secs2
                  FROM tblTask WHERE tblTask.mdtl_id is null GROUP BY tblTask.pcd_id, tblTask.stat_id)  AS h ON (g.stat_id = h.stat_id) AND (g.pcd_id = h.pcd_id)
WHERE (((g.mdtl_id) Is Not Null))
GROUP BY g.stat_id, g.pcd_id
PIVOT g.mdtl_id;

 

[plog]

Sorry - There were two examples in post #9 - pcd_id = 131, stat_id = 18

You're right. I'm the one who confused myself--sorry. Glad you got it worked out.