Need to query to take percentage of entries for fields

Posted by Jeena under Sql Server on 6/12/2012 | Points: 10 | Views : 1071 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi all,
I have 3 tables in MySQL..One master table and 2 child tables.Master table having a primary key and its a foreign key in other 2 child tables.
Child table can have multiple details corresponding to a master data entry..
1) Master table

property_id nbr_beds sqrft
1 1 2,000
2 1

2) child table 1

[ id ] [property_id] [tax_val ]
[1 ] [1 ] [ 200]
[ 1 ] [1 ]

3) child table 2

id property_id sale_val
1 1 2000
1 1
I need to take a report like how many % of records having entries for sale_val ,tax_val,sqrt like that..
I have used like select count(*),count(sqrft) from table name like that..So i will get the no of total records and the no of entries which is having value for sqrt .
But i don't know how to take it for the child tables as its having multiple entries..

I need the report as follows

Total Record count(bed) count(sqrft) count(tax_val) count(sale_val)
2 2 1 1 1

Is it possible to take a report like that??

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: CGN007 on: 6/12/2012 [Member] Silver | Points: 25

0	Please share your current query ... Jeena, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jeena on: 6/12/2012 [Member] Starter | Points: 25

0	select count(property_id) as Total Record,count(nbr_beds) as noofbeds ,count(sqrft) from Master table Jeena, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: CGN007 on: 6/12/2012 [Member] Silver | Points: 25

0	create and insert scrips also... Jeena, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jeena on: 6/12/2012 [Member] Starter | Points: 25

0	hi I am showing here a part of my table only... CREATE TABLE `general_info` ( `PropertyId` bigint(20) NOT NULL AUTO_INCREMENT, `bsmt_unfin_sqft` double DEFAULT NULL, PRIMARY KEY (`PropertyId`) ) ENGINE=InnoDB AUTO_INCREMENT=1436929 DEFAULT CHARSET=latin1; insert into general_info(bsmt_unfin_sqft) values(1000) CREATE TABLE `sale_info` ( `property_id` bigint(20) DEFAULT NULL, `sale_id` bigint(20) NOT NULL AUTO_INCREMENT, `grantee` longtext, PRIMARY KEY (`sale_id`), CONSTRAINT `nfo_fk` FOREIGN KEY (`property_id`) REFERENCES `general_info` (`PropertyId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1770164 DEFAULT CHARSET=latin1; insert into sale_info(property_id,grantee) values(1436929,'test') Thanks, Jeena Jeena, if this helps please login to Mark As Answer. \| Alert Moderator

hi
I am showing here a part of my table only...

CREATE TABLE `general_info` (
`PropertyId` bigint(20) NOT NULL AUTO_INCREMENT,
`bsmt_unfin_sqft` double DEFAULT NULL,
PRIMARY KEY (`PropertyId`)
) ENGINE=InnoDB AUTO_INCREMENT=1436929 DEFAULT CHARSET=latin1;

insert into general_info(bsmt_unfin_sqft) values(1000)

CREATE TABLE `sale_info` (
`property_id` bigint(20) DEFAULT NULL,
`sale_id` bigint(20) NOT NULL AUTO_INCREMENT,
`grantee` longtext,
PRIMARY KEY (`sale_id`),
CONSTRAINT `nfo_fk` FOREIGN KEY (`property_id`) REFERENCES `general_info` (`PropertyId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1770164 DEFAULT CHARSET=latin1;

insert into sale_info(property_id,grantee) values(1436929,'test')

Thanks,
Jeena

Jeena, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts