0

I am trying to modify my reports mysql query, the current query is :

select `companies`.`companynameturkish` AS `Company_Name`, (round(count(`applications`.`company_id`) / (select count(*) from applications where ((`applications`.`appstatus` = '3') ))*100)) AS `Work Load`,
sum(`applications`.`netprice`) AS `Total Cost`,`companies`.`percentage` AS `Firm %`
, round(((sum(`applications`.`netprice`) * 49) / 100),2) AS `Comission`
, round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Benefits`
, round(((sum(`applications`.`netprice`) * 49) / 100),2) - round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Diff`
, count(`applications`.`company_id`) AS `Quantity`
from (`applications` left join `companies` on((`applications`.`company_id` = `companies`.`id`)))
where ((`applications`.`appstatus` = '3') and (`applications`.`recordcreationdate`
like concat(curdate(),'%'))) group by `applications`.`company_id` order by `Work Load` desc

And here is the explain :

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY applications    NULL    ALL company_id  NULL    NULL    NULL    6   16.67   Using where; Using temporary; Using filesort
1   PRIMARY companies   NULL    eq_ref  PRIMARY PRIMARY 4   db.applications.company_id  1   100.00  Using where
2   SUBQUERY    applications    NULL    ALL NULL    NULL    NULL    NULL    6   16.67   Using where

What I am trying to do is to add new column and group by it, that column is : provider type (applications.type), so applications grouped by applications.type left join tbl_provider on applications.type = tbl_provider.id, selecting tbl_provider.provider_name for this column , these are my tables structure to help you know what I am talking about :

CREATE TABLE `applications` (
  `serial` int(11) NOT NULL AUTO_INCREMENT,
  `policy_no` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `company_id` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dataentry` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `creator_id` int(11) DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `type` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `provider_percentage` int(11) DEFAULT NULL,
  `firstname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fathername` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `passportno` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_main` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_sub` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `length` tinyint(1) DEFAULT NULL,
  `price` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `paid_price` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `netprice` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `saved_price` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `confirmed` tinyint(1) DEFAULT NULL,
  `appstatus` tinyint(1) DEFAULT NULL,
  `startdate` date DEFAULT NULL,
  `enddate` datetime DEFAULT NULL,
  `recordcreationdate` datetime DEFAULT NULL,
  `recordlastediteddate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `cancel_reason` text COLLATE utf8_unicode_ci,
  `image_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `net_price_deduced` tinyint(1) NOT NULL DEFAULT '0',
  `status_change_1_to_3` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`serial`),
  KEY `company_id` (`company_id`),
  KEY `policy_no` (`policy_no`),
  KEY `type` (`type`),
  FULLTEXT KEY `policy_no_ft` (`policy_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `manager_id` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `username` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `password` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `companynamearabic` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `companynameturkish` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `managername` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `companyspeciality` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `percentage` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `companytelephone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `companyemail` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `contactname` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `contactphone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `website` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `status` tinyint(11) DEFAULT NULL,
  `balancetype` tinyint(11) DEFAULT NULL,
  `companylimit` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `balance` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `recordcreationdate` date DEFAULT NULL,
  `recordlastediteddate` date DEFAULT NULL,
  `auto_approve` tinyint(4) DEFAULT NULL,
  `avl2mrkz` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `tbl_provider` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider_name` varchar(225) COLLATE utf8_bin DEFAULT NULL,
  `no_of_slabs` int(11) DEFAULT NULL,
  `age_from` int(11) DEFAULT NULL,
  `age_to` int(11) DEFAULT NULL,
  `provider_percentage` double DEFAULT NULL,
  `provider_income` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Thank you very much !

  • 1
    You're more likely to get a response if you cut down your question to the core of what you need to know. Is this a minimal example demonstrating your issue? – Martin Smith May 31 '17 at 19:39
0

It sounds like what you want to do is tie in tbl_provider to the existing query, via a LEFT JOIN on applications.type = tbl_provider.id, and adding provider_name from tbl_provider to the SELECT list, and to the GROUP BY clause.

NOTE: With a LEFT JOIN, if there's no matching provider, provider_name will be NULL. It should still appear in the SELECT list, and can still be grouped on, but it seemed worth noting.

Basically, to group on multiple columns, you just need to list all the columns after GROUP BY.

I believe this will do what you're looking for, if the above is correct (query reformatted slightly for readability):

select `companies`.`companynameturkish` AS `Company_Name`
      ,`tbl_provider`.`provider_name`
      ,(round(count(`applications`.`company_id`) / (select count(*) from applications where ((`applications`.`appstatus` = '3') ))*100)) AS `Work Load`
      ,sum(`applications`.`netprice`) AS `Total Cost`
      ,`companies`.`percentage` AS `Firm %`
      ,round(((sum(`applications`.`netprice`) * 49) / 100),2) AS `Comission`
      ,round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Benefits`
      ,round(((sum(`applications`.`netprice`) * 49) / 100),2) - round(((sum(`applications`.`netprice`) * `companies`.`percentage`) / 100),2) AS `Diff`
      ,count(`applications`.`company_id`) AS `Quantity`
  from ((`applications` left join `companies` on (`applications`.`company_id` = `companies`.`id`))
                        left join `tbl_provider` on (`applications`.`type` = `tbl_provider`.`id`))
 where (    (`applications`.`appstatus` = '3')
        and (`applications`.`recordcreationdate` like concat(curdate(),'%'))
       )
 group by `applications`.`company_id`, `tbl_provider`.`provider_name`
 order by `Work Load` desc

I've added tbl_provider.provider_name as the second item in the SELECT list, added the LEFT JOIN to tbl_provider, and added tbl_provider.provider_name as the second item in the GROUP BY as well.

NOTE: code untested, typos possible.

  • No typos at all, it worked the way intended, thank you very much sir ! – Fanar Web May 31 '17 at 23:01
  • Question sir, if I wanted to replace the column "Firm % " source, getting it from a new table, I mean from this one : CREATE TABLE company_percentage ( id int(11) NOT NULL AUTO_INCREMENT, company_id int(11) NOT NULL, provider_id int(11) NOT NULL, percentage varchar(25) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (id), KEY company_id (company_id), KEY provider_id (provider_id) ) ; so instead of getting Firm % from companies.percentage I want to get it according to provider from table above,What can I do ? – Fanar Web May 31 '17 at 23:31
  • LEFT JOIN ``company_percentage`` ON (``companies``.``id`` = ``company_percentage``.``company_id`` AND ``tbl_provider``.``id`` = ``company_percentage``.``provider_id``) - then, replace the reference to companies.percentage with company_percentage.percentage where needed. Note - this does assume that there will be one entry in company_percentage for each company and provider - if there's no entries, you'll get back NULL, and if there's multiple entries, one will be used at random (or you'll have to add the percentage to the group by, and you'll get multiple rows) – RDFozz May 31 '17 at 23:38
  • I am really grateful, thank you sir, thank you very much ! – Fanar Web May 31 '17 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.