on 02-10-2009 5:09 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.