troubleshooting Question

MySQL Query Help

Avatar of bdhtechnology
bdhtechnology asked on
MySQL ServerSQL
9 Comments1 Solution123 ViewsLast Modified:
I have the following query that is not working the way I would like.  I would like each provider printed out with 0 in the columns if they do not have a associated value.

Here is the query I have thus far:
SELECT IFNULL(SUM(a.trans_ttl),0) as `ttl`, service_category, provider 
FROM code_map b LEFT JOIN ( SELECT COUNT(*) AS `trans_ttl`, service_code, provider 
FROM transaction_data 
WHERE site_id = 6 
AND tran_date >= '2018-01-01 00:00:00'
AND tran_date <= '2018-12-31 23:59:59'
AND impacts = 'P' and type = 'S' and status = 'A' 
GROUP BY service_code, provider ) a ON b.service_code=a.service_code 
WHERE b.site_id = 6
GROUP BY b.service_category, provider
ORDER BY b.service_category, provider
LIMIT 0,100

Which returns:
+-----+--------------------+------------+
| ttl | service_category   | provider   |
+-----+--------------------+------------+
|   0 | AdProphy           | NULL       |
| 328 | AdProphy           | Provider 1 |
| 352 | AdProphy           | Provider 3 |
|  57 | AdSeal             | Provider 1 |
| 106 | AdSeal             | Provider 3 |
|   0 | AllExams           | NULL       |
| 465 | AllExams           | Provider 1 |
|  15 | AllExams           | Provider 2 |
| 496 | AllExams           | Provider 3 |
|   0 | Amalgam            | NULL       |
|   0 | Arestin            | NULL       |
|   0 | Bridge             | NULL       |
|  16 | Bridge             | Provider 2 |
|   0 | CHProphy           | NULL       |
|  63 | CHProphy           | Provider 1 |
|  73 | CHProphy           | Provider 3 |
|   0 | Conscious Sedation | NULL       |
|   0 | Cosmetic           | NULL       |
|   5 | Cosmetic           | Provider 2 |
|   0 | Crown              | NULL       |
| 142 | Crown              | Provider 2 |
|   0 | CrownBuild         | NULL       |
|  32 | CrownBuild         | Provider 2 |
|   0 | CrownCos           | NULL       |
|   1 | CrownCos           | Provider 2 |
|   0 | CrownImp           | NULL       |
|   0 | CrownRedo          | NULL       |
|   2 | CrownRedo          | Provider 2 |
|   0 | CrownSS            | NULL       |
|   1 | CrownSS            | Provider 2 |
|   0 | DentureComp        | NULL       |
|   7 | DentureComp        | Provider 2 |
|   0 | DenturePart        | NULL       |
|   4 | DenturePart        | Provider 2 |
|   0 | DentureRedo        | NULL       |
|   0 | DentureReline      | NULL       |
|   6 | DentureReline      | Provider 2 |
|   0 | DHP                | NULL       |
|   0 | DHPNew             | NULL       |
|   0 | Emerg              | NULL       |
|  21 | Emerg              | Provider 1 |
|  15 | Emerg              | Provider 2 |
|  23 | Emerg              | Provider 3 |
|  13 | Endo               | Provider 2 |
|   2 | EndoMolar          | Provider 2 |
|  39 | ExtSimp            | Provider 2 |
|   0 | ExtSurg            | NULL       |
|   2 | ExtSurg            | Provider 2 |
|   0 | Fluoride           | NULL       |
| 379 | Fluoride           | Provider 1 |
| 413 | Fluoride           | Provider 3 |
|   0 | Implant            | NULL       |
|   4 | Implant            | Provider 2 |
|   0 | MCOrthoCase        | NULL       |
|   1 | Nitrous            | Provider 1 |
|  32 | Nitrous            | Provider 2 |
|   1 | Nitrous            | Provider 3 |
|  24 | NPAMC              | Provider 1 |
|  23 | NPAMC              | Provider 3 |
|   0 | NPEMR              | NULL       |
|  46 | NPEMR              | Provider 1 |
|   1 | NPEMR              | Provider 2 |
|  47 | NPEMR              | Provider 3 |
|   0 | NPMC               | NULL       |
|   2 | NPMC               | Provider 1 |
|   3 | NPMC               | Provider 3 |
|   0 | NPPP               | NULL       |
|  88 | NPPP               | Provider 1 |
|  93 | NPPP               | Provider 3 |
|   0 | NPTOT              | NULL       |
| 136 | NPTOT              | Provider 1 |
|   1 | NPTOT              | Provider 2 |
| 143 | NPTOT              | Provider 3 |
|   0 | OccGuard           | NULL       |
|   2 | OccGuard           | Provider 2 |
| 133 | OCS                | Provider 1 |
| 161 | OCS                | Provider 3 |
|   0 | Oracare            | NULL       |
|  37 | Oracare            | Provider 1 |
|  13 | Oracare            | Provider 2 |
|  36 | Oracare            | Provider 3 |
|   0 | Oracare3           | NULL       |
|   0 | ORTHInv            | NULL       |
|   0 | OrthoConMC         | NULL       |
|   1 | OrthoConPP         | Provider 1 |
|   6 | PerEV              | Provider 1 |
|   1 | PerEV              | Provider 3 |
|  18 | PerGN              | Provider 1 |
|  22 | PerGN              | Provider 3 |
|   0 | PerioAR            | NULL       |
|   7 | PerLT              | Provider 1 |
|   5 | PerLT              | Provider 3 |
|  28 | PerMA              | Provider 1 |
|  37 | PerMA              | Provider 3 |
|   0 | PPOrthoCase        | NULL       |
|   5 | ProToothBrush      | Provider 1 |
|   6 | ProToothBrush      | Provider 3 |
|  61 | RCWFL              | Provider 1 |
|   1 | RCWFL              | Provider 2 |
|  73 | RCWFL              | Provider 3 |
| 350 | ReCare             | Provider 1 |
| 388 | ReCare             | Provider 3 |
|  65 | ResinAnt           | Provider 2 |
|   0 | ResinPost          | NULL       |
| 414 | ResinPost          | Provider 2 |
|   0 | ResinRedo          | NULL       |
|   8 | ResinRedo          | Provider 2 |
|   1 | RXFluoride         | Provider 1 |
|   2 | RXFluoride         | Provider 3 |
|   0 | SA                 | NULL       |
|   0 | SAAccept           | NULL       |
|   0 | SAAM               | NULL       |
|   0 | SADecline          | NULL       |
|   0 | SANonCand          | NULL       |
|   0 | SAScreen           | NULL       |
|   0 | Sealant            | NULL       |
| 193 | Sealant            | Provider 1 |
| 221 | Sealant            | Provider 3 |
|  32 | SealantRedo        | Provider 1 |
|  17 | SealantRedo        | Provider 3 |
|   0 | test               | NULL       |
|   2 | Veneer             | Provider 2 |
|  49 | WFL                | Provider 1 |
|  62 | WFL                | Provider 3 |
|   0 | White              | NULL       |
+-----+--------------------+------------+
125 rows in set (0.04 sec)
67 rows in set (0.03 sec)

I would like it to return something like the following:
+--------------------+--------------+--------------+--------------+
| service_category   | Provider 1   | Provider 2   | Provider 3   |
+--------------------+--------------+--------------+--------------+
| AdProphy           | 328          | 0            | 328          |
| AdSeal             | 57           | 0            | 352          |
+--------------------+--------------+--------------+--------------+

code_map structure:

CREATE TABLE `code_map` (
  `id` int(5) NOT NULL COMMENT 'row_id',
  `client_id` int(5) NOT NULL,
  `site_id` int(10) NOT NULL,
  `service_category` varchar(30) NOT NULL COMMENT 'service category',
  `service_code` varchar(30) NOT NULL COMMENT 'service_code'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

transaction_data structure:

CREATE TABLE `transaction_data` (
  `site_id` int(5) NOT NULL COMMENT 'site id number',
  `tran_num` int(10) NOT NULL COMMENT 'local transaction number',
  `status` varchar(30) NOT NULL,
  `fee` decimal(8,2) NOT NULL,
  `amount` decimal(8,2) NOT NULL COMMENT 'dollar amount of transaction',
  `service_code` varchar(20) NOT NULL COMMENT 'service code from ppm',
  `tran_date` datetime NOT NULL,
  `impacts` varchar(30) NOT NULL,
  `type` varchar(30) NOT NULL,
  `provider_id` varchar(9) DEFAULT NULL,
  `provider` varchar(100) NOT NULL,
  `provider_position` varchar(50) NOT NULL,
  `pay_type` varchar(45) NOT NULL,
  `patient_id` varchar(40) DEFAULT NULL,
  `adjustment_type` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY HASH (site_id)
PARTITIONS 20;

I believe I am missing something in my join but I can't for the life of me figure out what exactly.
transaction_data.sql
code_map.sql
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros