cancel
Showing results for 
Search instead for 
Did you mean: 

Crosstab counting two seperate tables

Former Member
0 Kudos

Our streetlighting inspection is based on areas. I have a table with the lighting inventory and can do a crosstab showing me how many columns per inspection area.

The recorded defects from the inspection are held in a different table and I can do a crosstab showing defects per inspection area.

I would like to end up with a report where the inspection area, No of Columns and No of defects are all on the one line.

Any suggestions on how I should approach this.

Thanks

Ron

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try something like this:


select area, sum(columns) as columns, sum(isnull(defects,0)) as defects
from lighting_inventory

left outer join inspections
on lighting_inventory.area = inspections.area

(I'm assuming all areas in inspections will be in lighting_inventory...)

HTH,

Carl

Answers (1)

Answers (1)

Former Member
0 Kudos

If you have any link between the tables then add both the fields in the crosstab in field to summarize and use the summary option as count.

Regards,

Raghavendra