Need to query to take percentage of entries for fields

Posted by Jeena under Sql Server on 6/12/2012 | Points: 10 | Views : 968 | Status : [Member] | Replies : 4
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??




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response