Statistical query-sql

Select
--Total number of registered persons
(Select COUNT (*) from [Yyd_users_reginfo]) as TotalCount,
--PC Terminal number of registered
(Select COUNT (*) from [yyd_users_reginfo] where regplatform = ' pc ') as Pctotalcount,
--Number of registered mobile phone terminals
(Select COUNT (*) from [yyd_users_reginfo] where regplatform = ' mobile ') as Mobiletotalcount,
--new total number of registrations
(Select COUNT (*) from [Yyd_users_reginfo]
where Convert (varchar (), regtime,120) >=convert (varchar (10), ' 2016-07-01 ', 120)
and Convert (varchar (), regtime,120) <=convert (varchar (ten), ' 2016-07-06 ',]) as Addtotalcount,
--New PC-Side registration number:
(Select COUNT (*) from [yyd_users_reginfo] where regplatform = ' PC '
and Convert (varchar (), regtime,120) >=convert (varchar (10), ' 2016-07-01 ', 120)
and Convert (varchar (), regtime,120) <=convert (varchar (ten), ' 2016-07-06 ',]) as Addpctotalcount,
--New mobile-Phone registration number
(Select COUNT (*) from [yyd_users_reginfo] where regplatform = ' mobile '
and Convert (varchar (), regtime,120) >=convert (varchar (10), ' 2016-07-01 ', 120)
and Convert (varchar (), regtime,120) <=convert (varchar (ten), ' 2016-07-06 ',]) as Addmobiletotalcount,

--Total number of activations
(select COUNT (distinct user_id) from [yyd_account_verification] where status =1) as Totalverification,
--Real name authentication number
(select COUNT (distinct user_id) from [yyd_account_verification] where vtype= ' ver_realname ' and Status=1) as Totalrealnameverification,
--Total number of new activations
(select COUNT (distinct user_id) from [yyd_account_verification] where status =1
and Convert (varchar (), createtime,120) >=convert (varchar (10), ' 2016-07-01 ', 120)
and Convert (varchar (), createtime,120) <=convert (varchar (ten), ' 2016-07-06 ',]) as Addtotalverification,
--New real name authentication number
(select COUNT (distinct user_id) from [yyd_account_verification] where vtype= ' ver_realname ' and Status=1
and Convert (varchar (), createtime,120) >=convert (varchar (10), ' 2016-07-01 ', 120)
and Convert (varchar), createtime,120) <=convert (varchar (ten), ' 2016-07-06 ', ")" as Addtotalrealnameverification,
--Cumulative number of accounts
(select COUNT (distinct user_id) from [Yyd_account_bankaccount] where status =1) as Openanaccount


--Recharge statistics
Select top 1 (SELECT COUNT (*) from (SELECT distinct user_id from Yyd_account_moneyrecord where (moneytype= ' recharge ' or Moneytyp e= ' online recharge ') and state=1) a) people,
(SELECT COUNT (*) from (SELECT distinct user_id from Yyd_account_moneyrecord where (moneytype= ' recharge ' or moneytype= ') and State=1 and Createtime >=convert (varchar (+), ' 2016-06-29 00:00:00 ', ') and Createtime <=convert (varchar (100), ' 2016-07-06 23:59:59 ', ()) a) Newpeople,
(SELECT COUNT (*) from Yyd_account_moneyrecord where (moneytype= ' recharge ' or moneytype= ') and state=1) Number,
(SELECT COUNT (*) from Yyd_account_moneyrecord where (moneytype= ' recharge ' or moneytype= ') and state=1 and Createtime >=c Onvert (varchar (+), ' 2016-06-29 00:00:00 ', ') and Createtime <=convert (varchar (100), ' 2016-07-06 23:59:59 ', 20)) Newnumber,
(select sum (amount) from Yyd_account_moneyrecord where (moneytype= ' recharge ' or moneytype= ') and state=1) Amount,
(select sum (amount) from Yyd_account_moneyrecord where (moneytype= ' recharge ' or moneytype= ') and state=1 and Createtime ; =convert (varchar (+), ' 2016-06-29 00:00:00 ', ") and Createtime <=convert (varchar (100), ' 2016-07-06 23:59:59 ', 20) ) Newamount,
(Select COUNT (*) from Yyd_account_moneyrecord m inner join Yyd_account_rechargerecord R on R.id=m.refid where (moneytype= ' Recharge ' or moneytype= ') and m.state=1 and (r.recharge_type= ' 0 ' or recharge_type= ' chinapay_b2c ' or recharge_type= ' Offline ')) Pccount,
(Select COUNT (*) from Yyd_account_moneyrecord m inner join Yyd_account_rechargerecord R on R.id=m.refid where (moneytype= ' Recharge ' or moneytype= ') and m.state=1 and r.recharge_type= ' Chinapaywap ') Mobilecount,
(select SUM (m.amount) from Yyd_account_moneyrecord m inner joins Yyd_account_rechargerecord R on R.id=m.refid where (Moneyt Ype= ' recharge ' or moneytype= ' on the line ') and m.state=1 and r.recharge_type= ' Chinapaywap ') Mobileamount,
(select SUM (m.amount) from Yyd_account_moneyrecord m inner joins Yyd_account_rechargerecord R on R.id=m.refid where (Moneyt Ype= ' recharge ' or moneytype= ' on the line ') and m.state=1 and (r.recharge_type= ' 0 ' or recharge_type= ' chinapay_b2c ' or recharge_type= ' Offline ')) Pcamount,
(Select COUNT (*) from Yyd_account_moneyrecord m inner join Yyd_account_rechargerecord R on R.id=m.refid where (moneytype= ' Recharge ' or moneytype= ') and m.state=1 and (r.recharge_type= ' 0 ' or recharge_type= ' chinapay_b2c ' or recharge_type= ' Offline ') and M.createtime >=convert (varchar (+), ' 2016-06-29 00:00:00 ', ') and M.createtime <=convert (varchar ( , ' 2016-07-06 23:59:59 ', ') Newpccount,
(Select COUNT (*) from Yyd_account_moneyrecord m inner join Yyd_account_rechargerecord R on R.id=m.refid where (moneytype= ' Recharge ' or moneytype= ' under the line ') and m.state=1 and r.recharge_type= ' Chinapaywap ' and m.createtime >=convert (varchar (100), ' 2016-06-29 00:00:00 ', ') and M.createtime <=convert (varchar (+), ' 2016-07-06 23:59:59 '), Newmobilecount,
(select SUM (m.amount) from Yyd_account_moneyrecord m inner joins Yyd_account_rechargerecord R on R.id=m.refid where (Moneyt Ype= ' recharge ' or moneytype= ' on the line ') and m.state=1 and r.recharge_type= ' Chinapaywap ' and m.createtime >=convert (varchar (100 ), ' 2016-06-29 00:00:00 ', ' and m.createtime <=convert (varchar (+), ' 2016-07-06 23:59:59 ', ') ' Newmobileamount,
(select SUM (m.amount) from Yyd_account_moneyrecord m inner joins Yyd_account_rechargerecord R on R.id=m.refid where (Moneyt Ype= ' recharge ' or moneytype= ' on the line ') and m.state=1 and (r.recharge_type= ' 0 ' or recharge_type= ' chinapay_b2c ' or recharge_type= ' Offline ') and M.createtime >=convert (varchar (+), ' 2016-06-29 00:00:00 ', ') and M.createtime <=convert (varchar ( (), ' 2016-07-06 23:59:59 ', ') newpcamount
From Yyd_account_moneyrecord


--Investment statistics
Select top 1
(Select COUNT (*) from Yyd_borrow_bidrecord) Bidcount,
(Select COUNT (*) from (SELECT distinct bid_user_id from Yyd_borrow_bidrecord) data) Biduserscount,
(select sum (amount) from Yyd_borrow_bidrecord) Bidamount,
(select SUM (repay_amount) from Yyd_borrow_repayrecord where status=0 and repaytype= ' principal ') Dhrepayamount,
(Select COUNT (*) from Yyd_borrow_bidrecord where 1=1 {0}) Newbidcount,
(Select COUNT (*) from (SELECT distinct bid_user_id from Yyd_borrow_bidrecord where 1=1 {0}) data) Newbiduserscount,
(select sum (amount) from Yyd_borrow_bidrecord where 1=1 {0}) Newbidamount,
(select SUM (repay_amount) from Yyd_borrow_repayrecord where Status=1 and repaytype= ' principal ') Yhrepayamount
From Yyd_borrow_bidrecord

Statistical query-sql