2

I'm facing a problem with a query.

I'm using phpMyAdmin and MySQL.

I'm trying to make a report of all my active clients (in ___Kardex where KDX_Status='active') and :

  • count the number of booking they made (in ___Bookings where BOO_Status!='cancel').
  • count the number of night they passed (in ___Bookings where BOO_Status!='cancel').

For clarity, here is a sample dataset 

CREATE TABLE `___Bookings` (
  `BOO_Id` int(10) NOT NULL AUTO_INCREMENT,
  `BOO_HotelId` varchar(20) NOT NULL,
  `BOO_ClientId` int(10) NOT NULL,
  `BOO_CompanyId` int(10) NOT NULL,
  `BOO_BillingId` int(10) NOT NULL,
  `BOO_DateCI` date NOT NULL,
  `BOO_DateCO` date NOT NULL,
  `BOO_Status` enum('confirmed','notconfirmed','option','cancel','checkin','checkout') NOT NULL,
  UNIQUE KEY `BOO_Id` (`BOO_Id`),
  KEY `id` (`BOO_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;

INSERT INTO `___Bookings` VALUES 
(70,'cus_CNHLMiMOzP5cuM',18,0,30,'2018-03-07','2018-03-12','confirmed'),
(71,'cus_CNHLMiMOzP5cuM',61,62,0,'2018-03-01','2018-03-02','cancel'),
(72,'cus_CNHLMiMOzP5cuM',19,0,0,'2018-03-04','2018-03-06','confirmed'),
(73,'cus_CNHLMiMOzP5cuM',61,0,0,'2018-03-01','2018-03-09','notconfirmed'),
(74,'cus_CNHLMiMOzP5cuM',61,0,0,'2018-03-10','2018-03-11','notconfirmed'),
 (75,'cus_CNHLMiMOzP5cuM',19,62,63,'2018-03-10','2018-03-21','option');

CREATE TABLE `___Hotels` (
  `HOT_HotelId` varchar(20) NOT NULL,
  `HOT_AutoLabel_VIP_Bookings` tinyint(4) NOT NULL,
  `HOT_AutoLabel_VIP_Nights` tinyint(4) NOT NULL,
  `HOT_AutoLabel_Regular_Bookings` tinyint(4) NOT NULL,
  `HOT_AutoLabel_Regular_Nights` tinyint(4) NOT NULL,
  `HOT_Status` enum('active','inactive','pending') NOT NULL,
  PRIMARY KEY (`HOT_HotelId`),
  UNIQUE KEY `HOT_HotelId` (`HOT_HotelId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `___Hotels` VALUES 
('cus_CNHLMiMOzP5cuM', 10, 15, 20, 25, 'active');

CREATE TABLE `___Kardex` (
  `KDX_Id` int(10) NOT NULL AUTO_INCREMENT,
  `KDX_HotelId` varchar(20) NOT NULL,
  `KDX_Type` enum('client','company','billing') NOT NULL,
  `KDX_Status` enum('active','inactive') NOT NULL,
  UNIQUE KEY `KDX_Id` (`KDX_Id`),
  KEY `id` (`KDX_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;

INSERT INTO `___Kardex` VALUES 
(18,'cus_CNHLMiMOzP5cuM','client','active'),
(19,'cus_CNHLMiMOzP5cuM','client','active'),
(30,'cus_CNHLMiMOzP5cuM','billing','active'),
(61,'cus_CNHLMiMOzP5cuM','client','active'),
(62,'cus_CNHLMiMOzP5cuM','company','inactive'),
(63,'cus_CNHLMiMOzP5cuM','company','active'),
(91,'cus_CNHLMiMOzP5cuM','company','active'),
(92,'cus_CNHLMiMOzP5cuM','company','active');

...and my best effort to date...

SELECT KDX_Id, KDX_Type,
  (
    SELECT COUNT(BOO_Id) 
    FROM ___Bookings 
    WHERE BOO_Status!='cancel' 
    AND (
      KDX_Id = ___Bookings.BOO_ClientId 
      OR KDX_Id = ___Bookings.BOO_CompanyId 
      OR KDX_Id = ___Bookings.BOO_BillingId 
        )
  ) AS nb_bookings,
  ( 
    SELECT SUM(DATEDIFF(___Bookings.BOO_DateCO, ___Bookings.BOO_DateCI)) 
    FROM ___Bookings 
    WHERE BOO_Status!='cancel' 
    AND (
        KDX_Id = ___Bookings.BOO_ClientId 
        OR KDX_Id = ___Bookings.BOO_CompanyId 
        OR KDX_Id = ___Bookings.BOO_BillingId
        )
  ) AS nb_nights, 
  HOT_HotelId, 
  HOT_AutoLabel_VIP_Bookings, 
  HOT_AutoLabel_VIP_Nights, 
  HOT_AutoLabel_Regular_Bookings, 
  HOT_AutoLabel_Regular_Nights 
FROM ___Kardex 
JOIN ___Hotels 
ON ___Kardex.KDX_HotelId = ___Hotels.HOT_HotelId 
JOIN ___Bookings 
ON ___Kardex.KDX_HotelId = ___Bookings.BOO_HotelId 
WHERE KDX_Status='active' 
AND HOT_Status='active' 
GROUP BY KDX_Id

And SQLFiddle of same:

http://sqlfiddle.com/#!9/67775f/1

The desired output should be like in my SQLFiddle above except the row 91 and 92 because I do not have any bookings or nights to show for these two entries.

Actually, the query returns me these rows with NULL or 0 entries.

expected results

| KDX_Id | KDX_Type | nb_bookings | nb_nights |        HOT_HotelId | HOT_AutoLabel_VIP_Bookings | HOT_AutoLabel_VIP_Nights | HOT_AutoLabel_Regular_Bookings | HOT_AutoLabel_Regular_Nights |
|--------|----------|-------------|-----------|--------------------|----------------------------|--------------------------|--------------------------------|------------------------------|
|     18 |   client |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     19 |   client |           2 |        13 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     30 |  billing |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     61 |   client |           2 |         9 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     63 |  company |           1 |        11 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |

Any help will be appreciated.

Thanks.

Improve this question
8
  • 1
    Your query doesn't life with SET sql_mode = CONCAT(@@sql_mode, ',only_full_group_by'); you will get a error KDX_Type' isn't in GROUP BY with other words your SQL isn't ansi GROUP BY SQL.. Meaning you can get unrelated data in your results. read this psce.com/en/blog/2012/05/15/… Commented Mar 19, 2018 at 13:15
  • @RaymondNijland, how can I change the query to take care of you comment please? Commented Mar 19, 2018 at 13:24
  • @Strawberry, thanks for editing my question. I tried to put some code, but SO rejected my post due to much code. :) Commented Mar 19, 2018 at 13:25
  • You're welcome. As an aside, I would encourage you to use InnoDB as opposed to MyISAM. There's really no good reason not to. Also, BOO_id is a PRIMARY KEY, and should be identified as such (and the INDEX on that column is redundant) Commented Mar 19, 2018 at 13:28
  • @Strawberry, if I change this, it cause some bugs after. Do I just need to change this into phpMyAdmin? Thanks. Commented Mar 19, 2018 at 13:30

2 Answers 2

Reset to default
3

I've rewritten the query so you don't need GROUP BY to filter out duplicates.

Query

SELECT 
   kardex_bookings.KDX_id
 , kardex_bookings.KDX_type
 , kardex_bookings.nb_bookings
 , kardex_bookings.nb_nights
 , hotels.HOT_HotelId
 , hotels.HOT_AutoLabel_VIP_Bookings
 , hotels.HOT_AutoLabel_VIP_Nights
 , hotels.HOT_AutoLabel_Regular_Bookings
 , hotels.HOT_AutoLabel_Regular_Nights

FROM ( 

  SELECT 
      kardex.KDX_id    
    , kardex.KDX_HotelId
    , kardex.KDX_type
    , kardex.KDX_Status
    , (
        SELECT
          COUNT(bookings.BOO_Id)
        FROM 
          ___Bookings bookings
        WHERE
            bookings.BOO_Status != 'cancel'
          AND (
              kardex.KDX_Id = bookings.BOO_ClientId 
            OR
              kardex.KDX_Id = bookings.BOO_CompanyId 
            OR
              kardex.KDX_Id = bookings.BOO_BillingId 
          )

      ) AS nb_bookings

    , ( 
        SELECT 
          SUM(DATEDIFF(bookings.BOO_DateCO, bookings.BOO_DateCI)) 
        FROM 
          ___Bookings bookings
        WHERE
          bookings.BOO_Status != 'cancel' 
          AND (
              kardex.KDX_Id = bookings.BOO_ClientId 
            OR
              kardex.KDX_Id = bookings.BOO_CompanyId 
            OR
              kardex.KDX_Id = bookings.BOO_BillingId 
          )    
      ) AS nb_nights    
  FROM 
    ___Kardex kardex     
) 
 AS kardex_bookings
INNER JOIN 
 ___Hotels hotels
ON 
 kardex_bookings.KDX_HotelId = hotels.HOT_HotelId

WHERE 
   kardex_bookings.KDX_Status = 'active'
 AND  
 # filter out non-bookings
   kardex_bookings.nb_bookings != 0
 AND
   kardex_bookings.nb_nights IS NOT NULL
 AND
   hotels.HOT_Status = 'active'

ORDER BY 
 kardex_bookings.KDX_Id ASC

Results

| KDX_id | KDX_type | nb_bookings | nb_nights |        HOT_HotelId | HOT_AutoLabel_VIP_Bookings | HOT_AutoLabel_VIP_Nights | HOT_AutoLabel_Regular_Bookings | HOT_AutoLabel_Regular_Nights |
|--------|----------|-------------|-----------|--------------------|----------------------------|--------------------------|--------------------------------|------------------------------|
|     18 |   client |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     19 |   client |           2 |        13 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     30 |  billing |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     61 |   client |           2 |         9 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     63 |  company |           1 |        11 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |

demo

http://sqlfiddle.com/#!9/67775f/56

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

A little bit hard for me to understand why my first query didn't work and why I need to use # filter now which is new for me. Could you please add a little more explanation please? Thanks a lot.
@Ross "why I need to use # filter now which is new for me" # filter out non-bookings is a comment in MySQL nothing more nothing less... And you need kardex_bookings.nb_bookings != 0 AND kardex_bookings.nb_nights IS NOT NULL to filter out the non bookings
0

Here i made certain changes to your query:

SELECT KDX_Id, KDX_Type,nb_bookings,nb_nights,
  HOT_HotelId, 
  HOT_AutoLabel_VIP_Bookings, 
  HOT_AutoLabel_VIP_Nights, 
  HOT_AutoLabel_Regular_Bookings, 
  HOT_AutoLabel_Regular_Nights 
FROM (
    SELECT KDX_Id, KDX_Type,COUNT(BOO_Id) AS nb_bookings,SUM(DATEDIFF(___Bookings.BOO_DateCO, ___Bookings.BOO_DateCI)) AS nb_nights,
    ___Kardex.KDX_HotelId
    FROM ___Bookings 
    INNER JOIN ___Kardex
    WHERE BOO_Status!='cancel' AND KDX_Status='active' 
    AND (
      KDX_Id = ___Bookings.BOO_ClientId 
      OR KDX_Id = ___Bookings.BOO_CompanyId 
      OR KDX_Id = ___Bookings.BOO_BillingId 
        )
        GROUP BY KDX_Id
 ) aa 
JOIN ___Hotels ON aa.KDX_HotelId = ___Hotels.HOT_HotelId  AND HOT_Status='active'

This is giving the result as you want. Please let me know if it works for you!! This is for the case of disabling 'only_full_group_by' sql_mode.

Improve this answer

4 Comments

You have a invalid ansi sql GROUP BY. Meaning you can get unrelated data in your results. read this psce.com/en/blog/2012/05/15/……
No, it won't. For the given question such condition will not arrive. If you think then could you please give the sample example?
Yes it does.. this query will fail with SET sql_mode = CONCAT(@@sql_mode, ',only_full_group_by'); Because KDX_Type and ___Kardex.KDX_HotelId is not named within your GROUP BY.. Without using that sql_mode MySQL is free to pick unrelated data for the columns KDX_Type and ___Kardex.KDX_HotelId
Thanks, Raymond!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.