-

SELECT tc.country_code, (SELECT COUNT(tb.tbl_id) FROM tbl_booking tb WHERE tb.country = tc.country_code) AS total_booking, COUNT(DISTINCT tbd.booking_id) AS confirmed_booking FROM tbl_country tc LEFT JOIN tbl_booking tb ON tc.country_code = tb.country LEFT JOIN tbl_booking_details tbd ON tb.tbl_id = tbd.booking_id AND tbd.pay_status = 1 AND tbd.booking_cancle_status = 0 GROUP BY tc.country_code;


SELECT tc.country_code, (SELECT COUNT(tb.tbl_id) FROM tbl_booking tb WHERE tb.country = tc.country_code) AS total_booking,

 COUNT(DISTINCT tbd.booking_id) AS confirmed_booking FROM tbl_country tc 

 JOIN tbl_booking tb ON tc.country_code = tb.country 

 JOIN tbl_booking_details tbd ON tb.tbl_id = tbd.booking_id AND tbd.pay_status = 1 AND tbd.booking_cancle_status = 0

 GROUP BY tc.country_code;


======================percentage

SELECT tc.country_code, (SELECT COUNT(tb.tbl_id) FROM tbl_booking tb WHERE tb.country = tc.country_code) AS total_booking, COUNT(DISTINCT tbd.booking_id) AS confirmed_booking, COUNT(DISTINCT tbd.booking_id) * 100.0 / NULLIF((SELECT COUNT(tb.tbl_id) FROM tbl_booking tb WHERE tb.country = tc.country_code), 0) AS confirmed_percentage FROM tbl_country tc LEFT JOIN tbl_booking tb ON tc.country_code = tb.country LEFT JOIN tbl_booking_details tbd ON tb.tbl_id = tbd.booking_id AND tbd.pay_status = 1 AND tbd.booking_cancle_status = 0 GROUP BY tc.country_code;

Comments

Post a Comment

Popular posts from this blog

How to Delete record using PHP Ajax

-
index.php ?   01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 <!DOCTYPE html> <html> <head>      <title></title> </head> <body>   <?php        $connection = mysqli_connect( "localhost" , "root" , "" , "db_2018" ) or die (mysqli_error());        $ReadSql = "SELECT * FROM `crud`" ;      $res = mysqli_query( $connection , $ReadSql );   ?>     <table>      <tr>          <th>name</th>          <th>Last Name</th>          <th>email id</th>          <th>gender</th>          <th>age</th>          <th>Action</th>      </tr>        <?php while ( $fetch = mysqli_fetch_array( $res )) {            extract( $fetch );         ?>
Read more

How to seperate character from string in php

-
?   1 2 3 4 5 6 7 8 9 $string = "abcdef" ; $result =[]; $strlen = strlen ( $string ); for ( $i =0; $i < $strlen ; $i ++) {      $result [ $i ]= $string [ $i ];   } print_r( $result );     $string = "abcdef"; $result=[]; $strlen = strlen($string); for($i=0;$i<$strlen;$i++) { $result[$i]=$string[$i]; } print_r($result);
Read more

Uploads Only 10 files in month step by step

-
?   01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 <?php        $con = mysqli_connect( "localhost" , "root" , "" , "demo" );   date_default_timezone_set( 'Asia/Kolkata' );        $date = Date ( "Y-m-d" );                  if (isset( $_POST [ 'submit' ]))      {          extract( $_POST );            $name1 = $_FILES [ 'photo' ][ 'name' ];    $type = $_FILES [ 'photo' ][ 'type' ];    $size = $_FILES [ 'photo' ][ 'size' ];    $temp = $_FILES [ 'photo' ][ 'tmp_name' ];    if ( $name1 ){   $upload = "posts/" ;              $imgExt = strtolower ( pathinfo ( $name1 , PATHINFO_EXTENSION));              $valid_ext = array ( 'jpg' , 'png' , 'jpeg' );
Read more