Link to home
Create AccountLog in
Avatar of bdhtechnology
bdhtechnology

asked on

MySQL Query Help

I have the following query that is working, however it does not return a b.service_category value when the SUM is 0:
SELECT a.`site_id`, b.service_category 
, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `interval1`
, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `interval2`
, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `interval3`
, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `interval4`
, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `interval5`
, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `interval6`
, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `interval7`
, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `interval8`
, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `interval9`
, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `interval10`
, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `interval11`
, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `interval12`
FROM transaction_data a, code_map b 
WHERE a.site_id = 6 
AND tran_date BETWEEN '2018-01-01' AND '2018-12-31' 
AND impacts = 'P' AND type = 'S' AND status = 'A' 
AND a.service_code = b.service_code 
AND a.site_id = b.site_id 
GROUP BY a.`site_id`, b.service_category 
ORDER BY a.`site_id`, b.service_category ;

Open in new window


I have been attempting to modify the query so it runs well and prints 0 for values.  The query below will return all of the service_category values, however it is not correctly computing the interval values:
SELECT *
FROM code_map b 
LEFT JOIN (
    SELECT COUNT(*) AS `trans_ttl`, service_code
	, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `interval1`
	, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `interval2`
	, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `interval3`
	, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `interval4`
	, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `interval5`
	, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `interval6`
	, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `interval7`
	, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `interval8`
	, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `interval9`
	, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `interval10`
	, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `interval11`
	, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `interval12`
    FROM transaction_data
    WHERE site_id = 6 
    AND tran_date BETWEEN '2018-01-01' AND '2018-12-31'
    AND impacts = 'P' AND type = 'S' AND status = 'A' 
    GROUP BY service_code
) a ON b.service_code=a.service_code
WHERE b.site_id = 6 
GROUP BY site_id, service_category 
ORDER BY site_id, service_category ;

Open in new window


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;

Open in new window


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;

Open in new window

I am sure I am doing something wrong but I have been stuck on this query for over a day now and no closer to solving the issue!  Any help would be appreciated.  I will work on sanitizing some data to post as well.
Avatar of PortletPaul
PortletPaul
Flag of Australia image

If you want an "outer join" (one that allows unmatched rows to be returned) then you MUST be careful with how you use the where clause.

This query, although it asks to use a left join:
    select *
    from A
    left join B on A.id = B.id
    where B.code = 'mustbethis'
cannot allow unmatched rows because B.code 'mustbethis' i.e. cannot be NULL

The following queries allow unmatched rows to be included in the results, i.e. the left join works as expected

select *
from A
left join B on A.id = B.id
where B.code = 'mustbethis' or B.code IS NULL

select *
from A
left join B on A.id = B.id and B.code = 'mustbethis'

You need to be careful with your where clause conditions.
>>"will return all of the service_category values, however it is not correctly computing the interval values:"

Regarding your second query:
How do you know the calculations are wrong?
Does the tran_date column include time of day?

Why do you have a group by clause at the end of the second query?  (all the sums are formed in the subquery)
Avatar of bdhtechnology
bdhtechnology

ASKER

I guess putting my question into words helped me realize how to do it.  Here is the query that works, maybe its not the most efficient query but it does work.  Takes 2.3 seconds and the transaction_data table has over 3 million rows.

SELECT site_id, c.service_category, IFNULL(trans_ttl, 0), IFNULL(trans_interval1, 0) as interval1 , IFNULL(trans_interval2, 0) as interval2 , IFNULL(trans_interval3, 0) as interval3 , IFNULL(trans_interval4, 0) as interval4 , IFNULL(trans_interval5, 0) as interval5 , IFNULL(trans_interval6, 0) as interval6 , IFNULL(trans_interval7, 0) as interval7 , IFNULL(trans_interval8, 0) as interval8 , IFNULL(trans_interval9, 0) as interval9 , IFNULL(trans_interval10, 0) as interval10 , IFNULL(trans_interval11, 0) as interval11 , IFNULL(trans_interval12, 0) as interval12 
FROM code_map c
LEFT JOIN (
    SELECT COUNT(*) AS `trans_ttl`, service_category
	, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `trans_interval1`
	, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `trans_interval2`
	, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `trans_interval3`
	, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `trans_interval4`
	, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `trans_interval5`
	, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `trans_interval6`
	, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `trans_interval7`
	, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `trans_interval8`
	, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `trans_interval9`
	, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `trans_interval10`
	, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `trans_interval11`
	, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `trans_interval12`
    FROM transaction_data a, code_map b 
    WHERE a.site_id = 6 
    AND a.service_code = b.service_code 
    AND a.site_id = b.site_id 
    AND tran_date BETWEEN '2018-01-01' AND '2018-12-31'
    AND impacts = 'P' AND type = 'S' AND status = 'A' 
    GROUP BY service_category
) t ON t.service_category=c.service_category
WHERE c.site_id = 6 
GROUP BY site_id, c.service_category 
ORDER BY site_id, c.service_category 
LIMIT 0, 100

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You already tried this query.
SELECT *
FROM code_map b 
LEFT JOIN (
    SELECT COUNT(*) AS `trans_ttl`, service_code
	, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `interval1`
	, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `interval2`
	, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `interval3`
	, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `interval4`
	, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `interval5`
	, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `interval6`
	, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `interval7`
	, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `interval8`
	, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `interval9`
	, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `interval10`
	, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `interval11`
	, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `interval12`
    FROM transaction_data
    WHERE site_id = 6 
    AND tran_date BETWEEN '2018-01-01' AND '2018-12-31'
    AND impacts = 'P' AND type = 'S' AND status = 'A' 
    GROUP BY service_code
) a ON b.service_code=a.service_code
WHERE b.site_id = 6 
GROUP BY site_id, service_category 
ORDER BY site_id, service_category ;

Open in new window

Can you explain what is not working in this? If you can provide some sample data from two tables, that helps in modifying the query.
Attached is sample data for each table.  Below is the output I receive from the last query I posted, which looks to be correct based on individual checks I have been doing.  I am also going to want a table returned that has each of these values for each `provider`, including the 0 values.   I will post another question for that one.

site_id    	service_category 	IFNULL(trans_ttl, 0) 	interval1 	interval2 	interval3 	interval4 	interval5 	interval6 	interval7 	interval8 	interval9 	interval10 	interval11 	interval12 	
6	AdProphy	680	0	0	0	0	0	0	166	172	129	173	40	0	
6	AdSeal	163	0	0	0	0	0	0	56	38	30	35	4	0	
6	AllExams	976	0	0	0	0	0	0	241	249	188	240	58	0	
6	Amalgam	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	Arestin	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	Bridge	16	0	0	0	0	0	0	0	3	9	4	0	0	
6	CHProphy	136	0	0	0	0	0	0	36	32	32	29	7	0	
6	Conscious Sedation	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	Cosmetic	5	0	0	0	0	0	0	0	0	3	2	0	0	
6	Crown	142	0	0	0	0	0	0	32	32	31	37	10	0	
6	CrownBuild	32	0	0	0	0	0	0	6	8	9	8	1	0	
6	CrownCos	1	0	0	0	0	0	0	0	0	0	1	0	0	
6	CrownImp	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	CrownRedo	2	0	0	0	0	0	0	2	0	0	0	0	0	
6	CrownSS	1	0	0	0	0	0	0	1	0	0	0	0	0	
6	DentureComp	7	0	0	0	0	0	0	0	2	3	2	0	0	
6	DenturePart	4	0	0	0	0	0	0	1	2	0	0	1	0	
6	DentureRedo	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	DentureReline	6	0	0	0	0	0	0	1	0	1	4	0	0	
6	DHP	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	DHPNew	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	Emerg	59	0	0	0	0	0	0	20	18	6	13	2	0	
6	Endo	13	0	0	0	0	0	0	1	1	7	3	1	0	
6	EndoMolar	2	0	0	0	0	0	0	0	0	1	1	0	0	
6	ExtSimp	39	0	0	0	0	0	0	3	13	7	15	1	0	
6	ExtSurg	2	0	0	0	0	0	0	1	1	0	0	0	0	
6	Fluoride	792	0	0	0	0	0	0	201	191	156	198	46	0	
6	Implant	4	0	0	0	0	0	0	0	0	0	4	0	0	
6	MCOrthoCase	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	Nitrous	34	0	0	0	0	0	0	4	4	11	13	2	0	
6	NPAMC	47	0	0	0	0	0	0	12	16	6	8	5	0	
6	NPEMR	94	0	0	0	0	0	0	29	15	29	16	5	0	
6	NPMC	5	0	0	0	0	0	0	1	2	2	0	0	0	
6	NPPP	181	0	0	0	0	0	0	49	39	35	45	13	0	
6	NPTOT	280	0	0	0	0	0	0	79	56	66	61	18	0	
6	OccGuard	2	0	0	0	0	0	0	1	0	1	0	0	0	
6	OCS	294	0	0	0	0	0	0	78	67	57	70	22	0	
6	Oracare	86	0	0	0	0	0	0	25	22	20	15	4	0	
6	Oracare3	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	ORTHInv	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	OrthoConMC	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	OrthoConPP	1	0	0	0	0	0	0	0	1	0	0	0	0	
6	PerEV	7	0	0	0	0	0	0	1	4	1	1	0	0	
6	PerGN	40	0	0	0	0	0	0	16	6	6	10	2	0	
6	PerioAR	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	PerLT	12	0	0	0	0	0	0	2	5	2	3	0	0	
6	PerMA	65	0	0	0	0	0	0	16	15	14	18	2	0	
6	PPOrthoCase	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	ProToothBrush	11	0	0	0	0	0	0	2	4	3	2	0	0	
6	RCWFL	135	0	0	0	0	0	0	26	37	28	34	10	0	
6	ReCare	738	0	0	0	0	0	0	173	185	151	190	39	0	
6	ResinAnt	65	0	0	0	0	0	0	5	12	19	25	4	0	
6	ResinPost	414	0	0	0	0	0	0	123	88	90	103	10	0	
6	ResinRedo	8	0	0	0	0	0	0	0	0	6	1	1	0	
6	RXFluoride	3	0	0	0	0	0	0	2	1	0	0	0	0	
6	SA	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	SAAccept	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	SAAM	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	SADecline	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	SANonCand	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	SAScreen	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	Sealant	414	0	0	0	0	0	0	150	103	101	49	11	0	
6	SealantRedo	49	0	0	0	0	0	0	4	11	19	12	3	0	
6	test	0	0	0	0	0	0	0	0	0	0	0	0	0	
6	Veneer	2	0	0	0	0	0	0	0	0	1	1	0	0	
6	WFL	111	0	0	0	0	0	0	35	22	20	25	9	0	
6	White	0	0	0	0	0	0	0	0	0	0	0	0	0	

Open in new window

code_map.sql
transaction_data.sql
@PortletPaul, your query returns all NULL values for the intervals, plus I am getting way more rows (353) than I should get which is 70 I believe.  I should only have 1 row for each unique value for `service_category` in `code_map`
Use COALESCE() or IFNULL() to overcome the numerous NULLs (which occur because of the left join)
SELECT
      id
    , client_id
    , site_id
    , service_category
    , b.service_code
    , coalesce(trans_ttl,0) trans_ttl
    , coalesce(interval1,0) interval1
    , coalesce(interval2,0) interval2
    , coalesce(interval3,0) interval3
    , coalesce(interval4,0) interval4
    , coalesce(interval5,0) interval5
    , coalesce(interval6,0) interval6
    , coalesce(interval7,0) interval7
    , coalesce(interval8,0) interval8
    , coalesce(interval9,0) interval9
    , coalesce(interval10,0) interval10
    , coalesce(interval11,0) interval11
    , coalesce(interval12,0) interval12
FROM code_map b 
LEFT JOIN (
    SELECT COUNT(*) AS `trans_ttl`, service_code
	, SUM( IF(tran_date BETWEEN '2018-01-01' AND '2018-01-31', 1, 0)) AS `interval1`
	, SUM( IF(tran_date BETWEEN '2018-02-01' AND '2018-02-28', 1, 0)) AS `interval2`
	, SUM( IF(tran_date BETWEEN '2018-03-01' AND '2018-03-31', 1, 0)) AS `interval3`
	, SUM( IF(tran_date BETWEEN '2018-04-01' AND '2018-04-30', 1, 0)) AS `interval4`
	, SUM( IF(tran_date BETWEEN '2018-05-01' AND '2018-05-31', 1, 0)) AS `interval5`
	, SUM( IF(tran_date BETWEEN '2018-06-01' AND '2018-06-30', 1, 0)) AS `interval6`
	, SUM( IF(tran_date BETWEEN '2018-07-01' AND '2018-07-31', 1, 0)) AS `interval7`
	, SUM( IF(tran_date BETWEEN '2018-08-01' AND '2018-08-31', 1, 0)) AS `interval8`
	, SUM( IF(tran_date BETWEEN '2018-09-01' AND '2018-09-30', 1, 0)) AS `interval9`
	, SUM( IF(tran_date BETWEEN '2018-10-01' AND '2018-10-31', 1, 0)) AS `interval10`
	, SUM( IF(tran_date BETWEEN '2018-11-01' AND '2018-11-30', 1, 0)) AS `interval11`
	, SUM( IF(tran_date BETWEEN '2018-12-01' AND '2018-12-31', 1, 0)) AS `interval12`
    FROM transaction_data
    WHERE site_id = 6 
    AND tran_date BETWEEN '2018-01-01' AND '2018-12-31'
    AND impacts = 'P' AND type = 'S' AND status = 'A' 
    GROUP BY service_code
) a ON b.service_code=a.service_code
WHERE b.site_id = 6 
GROUP BY site_id, service_category 
ORDER BY site_id, service_category 
;

Open in new window

+----+------+-----------+---------+--------------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
|    |  id  | client_id | site_id |  service_category  | service_code | trans_ttl | interval1 | interval2 | interval3 | interval4 | interval5 | interval6 | interval7 | interval8 | interval9 | interval10 | interval11 | interval12 |
+----+------+-----------+---------+--------------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
|  1 |  815 |         1 |       6 | AdProphy           | PrAxx        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|  2 | 2175 |         1 |       6 | AdSeal             | 1351A        |         4 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          4 |          0 |
|  3 | 6105 |         1 |       6 | AllExams           | D0120        |        29 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |         29 |          0 |
|  4 | 3328 |         1 |       6 | Amalgam            | D2140        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|  5 | 4764 |         1 |       6 | Arestin            | D4381        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|  6 | 2573 |         1 |       6 | Bridge             | D6210        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|  7 |  811 |         1 |       6 | CHProphy           | PrNMC        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|  8 | 5725 |         1 |       6 | Conscious Sedation | D9248        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|  9 | 3591 |         1 |       6 | Cosmetic           | D2740        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 10 | 2561 |         1 |       6 | Crown              | D2720        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 11 | 2609 |         1 |       6 | CrownBuild         | D2950        |         1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          1 |          0 |
| 12 | 3204 |         1 |       6 | CrownCos           | D2740        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 13 | 2590 |         1 |       6 | CrownImp           | D6058        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 14 | 3965 |         1 |       6 | CrownRedo          | 2740R        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 15 | 2607 |         1 |       6 | CrownSS            | D2930        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 16 | 2540 |         1 |       6 | DentureComp        | D5110        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 17 | 2552 |         1 |       6 | DenturePart        | D5211        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 18 | 3959 |         1 |       6 | DentureRedo        | 5110R        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 19 | 2612 |         1 |       6 | DentureReline      | D5730        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 20 |  839 |         1 |       6 | DHP                | DHPAC        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 21 | 8665 |         1 |       6 | DHPNew             | DHPAC        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 22 | 2516 |         1 |       6 | Emerg              | D0140        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 23 | 2453 |         1 |       6 | Endo               | D3310        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 24 | 2456 |         1 |       6 | EndoMolar          | D3330        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 25 | 2481 |         1 |       6 | ExtSimp            | D7140        |         1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          1 |          0 |
| 26 | 2488 |         1 |       6 | ExtSurg            | D7220        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 27 |  813 |         1 |       6 | Fluoride           | D1203        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 28 | 2559 |         1 |       6 | Implant            | D6010        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 29 |  840 |         1 |       6 | MCOrthoCase        | 8070M        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 30 | 2441 |         1 |       6 | Nitrous            | D9230        |         1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          1 |          0 |
| 31 | 1448 |         1 |       6 | NPAMC              | AMCNP        |         2 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          2 |          0 |
| 32 | 4733 |         1 |       6 | NPEMR              | NTAEX        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 33 |  807 |         1 |       6 | NPMC               | NMCEx        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 34 |  809 |         1 |       6 | NPPP               | NCExx        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 35 | 2310 |         1 |       6 | NPTOT              | NPAMC        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 36 | 2595 |         1 |       6 | OccGuard           | D9940        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 37 |  820 |         1 |       6 | OCS                | D0431        |         7 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          7 |          0 |
| 38 |  823 |         1 |       6 | Oracare            | ORACR        |         4 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          4 |          0 |
| 39 | 6035 |         1 |       6 | Oracare3           | OR3PK        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 40 | 2424 |         1 |       6 | ORTHInv            | 8080I        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 41 |  858 |         1 |       6 | OrthoConMC         | ORMCn        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 42 |  859 |         1 |       6 | OrthoConPP         | D8660        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 43 | 2149 |         1 |       6 | PerEV              | PerRe        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 44 |  822 |         1 |       6 | PerGN              | D4341        |         2 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          2 |          0 |
| 45 | 3190 |         1 |       6 | PerioAR            | D4381        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 46 |  812 |         1 |       6 | PerLT              | D4342        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 47 |  821 |         1 |       6 | PerMA              | D4910        |         1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          1 |          0 |
| 48 |  843 |         1 |       6 | PPOrthoCase        | D8090        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 49 | 4675 |         1 |       6 | ProToothBrush      | OralB        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 50 | 4714 |         1 |       6 | RCWFL              | RCWFL        |         7 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          7 |          0 |
| 51 | 2365 |         1 |       6 | ReCare             | D0120        |        29 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |         29 |          0 |
| 52 | 2521 |         1 |       6 | ResinAnt           | D2330        |         1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          1 |          0 |
| 53 | 2525 |         1 |       6 | ResinPost          | D2391        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 54 | 3951 |         1 |       6 | ResinRedo          | 2330R        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 55 |  837 |         1 |       6 | RXFluoride         | RXFLL        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 56 |  838 |         1 |       6 | SA                 | SAINS        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 57 |  863 |         1 |       6 | SAAccept           | SAAHT        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 58 |  866 |         1 |       6 | SAAM               | SAAMR        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 59 |  864 |         1 |       6 | SADecline          | SADHT        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 60 |  865 |         1 |       6 | SANonCand          | SAPNC        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 61 |  862 |         1 |       6 | SAScreen           | 99202        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 62 |  834 |         1 |       6 | Sealant            | D1351        |         7 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          7 |          0 |
| 63 | 3950 |         1 |       6 | SealantRedo        | 1351R        |         3 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          3 |          0 |
| 64 | 8785 |         1 |       6 | test               | test         |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 65 | 2572 |         1 |       6 | Veneer             | D2962        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
| 66 | 4700 |         1 |       6 | WFL                | NPWFL        |         8 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          8 |          0 |
| 67 | 2598 |         1 |       6 | White              | D9972        |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
+----+------+-----------+---------+--------------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+

Open in new window

see Permanent link: https://rextester.com/XDQ53169

----
Whilst I really appreciate the data, it isn't necessary to have all the data, just a "sample" is sufficient (in fact it makes it easier for you to verify if the sample is small).
 I could not use all the data provided, so the aggregations will not match your tables - I had to arbitrarily delete many of the inserts.
That fixes the 0 issue, however the counts are not correct for the service_category.  Below is what they should be from the subset of the table I posted:

+---------+--------------------+----------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
| site_id | service_category   | IFNULL(trans_ttl, 0) | interval1 | interval2 | interval3 | interval4 | interval5 | interval6 | interval7 | interval8 | interval9 | interval10 | interval11 | interval12 |
+---------+--------------------+----------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
|       6 | AdProphy           |                  680 |         0 |         0 |         0 |         0 |         0 |         0 |       166 |       172 |       129 |        173 |         40 |          0 |
|       6 | AdSeal             |                  163 |         0 |         0 |         0 |         0 |         0 |         0 |        56 |        38 |        30 |         35 |          4 |          0 |
|       6 | AllExams           |                  976 |         0 |         0 |         0 |         0 |         0 |         0 |       241 |       249 |       188 |        240 |         58 |          0 |
|       6 | Amalgam            |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | Arestin            |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | Bridge             |                   16 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         3 |         9 |          4 |          0 |          0 |
|       6 | CHProphy           |                  136 |         0 |         0 |         0 |         0 |         0 |         0 |        36 |        32 |        32 |         29 |          7 |          0 |
|       6 | Conscious Sedation |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | Cosmetic           |                    5 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         3 |          2 |          0 |          0 |
|       6 | Crown              |                  142 |         0 |         0 |         0 |         0 |         0 |         0 |        32 |        32 |        31 |         37 |         10 |          0 |
|       6 | CrownBuild         |                   32 |         0 |         0 |         0 |         0 |         0 |         0 |         6 |         8 |         9 |          8 |          1 |          0 |
|       6 | CrownCos           |                    1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          1 |          0 |          0 |
|       6 | CrownImp           |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | CrownRedo          |                    2 |         0 |         0 |         0 |         0 |         0 |         0 |         2 |         0 |         0 |          0 |          0 |          0 |
|       6 | CrownSS            |                    1 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         0 |         0 |          0 |          0 |          0 |
|       6 | DentureComp        |                    7 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         2 |         3 |          2 |          0 |          0 |
|       6 | DenturePart        |                    4 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         2 |         0 |          0 |          1 |          0 |
|       6 | DentureRedo        |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | DentureReline      |                    6 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         0 |         1 |          4 |          0 |          0 |
|       6 | DHP                |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | DHPNew             |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | Emerg              |                   59 |         0 |         0 |         0 |         0 |         0 |         0 |        20 |        18 |         6 |         13 |          2 |          0 |
|       6 | Endo               |                   13 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         1 |         7 |          3 |          1 |          0 |
|       6 | EndoMolar          |                    2 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |          1 |          0 |          0 |
|       6 | ExtSimp            |                   39 |         0 |         0 |         0 |         0 |         0 |         0 |         3 |        13 |         7 |         15 |          1 |          0 |
|       6 | ExtSurg            |                    2 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         1 |         0 |          0 |          0 |          0 |
|       6 | Fluoride           |                  792 |         0 |         0 |         0 |         0 |         0 |         0 |       201 |       191 |       156 |        198 |         46 |          0 |
|       6 | Implant            |                    4 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          4 |          0 |          0 |
|       6 | MCOrthoCase        |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | Nitrous            |                   34 |         0 |         0 |         0 |         0 |         0 |         0 |         4 |         4 |        11 |         13 |          2 |          0 |
|       6 | NPAMC              |                   47 |         0 |         0 |         0 |         0 |         0 |         0 |        12 |        16 |         6 |          8 |          5 |          0 |
|       6 | NPEMR              |                   94 |         0 |         0 |         0 |         0 |         0 |         0 |        29 |        15 |        29 |         16 |          5 |          0 |
|       6 | NPMC               |                    5 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         2 |         2 |          0 |          0 |          0 |
|       6 | NPPP               |                  181 |         0 |         0 |         0 |         0 |         0 |         0 |        49 |        39 |        35 |         45 |         13 |          0 |
|       6 | NPTOT              |                  280 |         0 |         0 |         0 |         0 |         0 |         0 |        79 |        56 |        66 |         61 |         18 |          0 |
|       6 | OccGuard           |                    2 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         0 |         1 |          0 |          0 |          0 |
|       6 | OCS                |                  294 |         0 |         0 |         0 |         0 |         0 |         0 |        78 |        67 |        57 |         70 |         22 |          0 |
|       6 | Oracare            |                   86 |         0 |         0 |         0 |         0 |         0 |         0 |        25 |        22 |        20 |         15 |          4 |          0 |
|       6 | Oracare3           |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | ORTHInv            |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | OrthoConMC         |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | OrthoConPP         |                    1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         0 |          0 |          0 |          0 |
|       6 | PerEV              |                    7 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         4 |         1 |          1 |          0 |          0 |
|       6 | PerGN              |                   40 |         0 |         0 |         0 |         0 |         0 |         0 |        16 |         6 |         6 |         10 |          2 |          0 |
|       6 | PerioAR            |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | PerLT              |                   12 |         0 |         0 |         0 |         0 |         0 |         0 |         2 |         5 |         2 |          3 |          0 |          0 |
|       6 | PerMA              |                   65 |         0 |         0 |         0 |         0 |         0 |         0 |        16 |        15 |        14 |         18 |          2 |          0 |
|       6 | PPOrthoCase        |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | ProToothBrush      |                   11 |         0 |         0 |         0 |         0 |         0 |         0 |         2 |         4 |         3 |          2 |          0 |          0 |
|       6 | RCWFL              |                  135 |         0 |         0 |         0 |         0 |         0 |         0 |        26 |        37 |        28 |         34 |         10 |          0 |
|       6 | ReCare             |                  738 |         0 |         0 |         0 |         0 |         0 |         0 |       173 |       185 |       151 |        190 |         39 |          0 |
|       6 | ResinAnt           |                   65 |         0 |         0 |         0 |         0 |         0 |         0 |         5 |        12 |        19 |         25 |          4 |          0 |
|       6 | ResinPost          |                  414 |         0 |         0 |         0 |         0 |         0 |         0 |       123 |        88 |        90 |        103 |         10 |          0 |
|       6 | ResinRedo          |                    8 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         6 |          1 |          1 |          0 |
|       6 | RXFluoride         |                    3 |         0 |         0 |         0 |         0 |         0 |         0 |         2 |         1 |         0 |          0 |          0 |          0 |
|       6 | SA                 |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | SAAccept           |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | SAAM               |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | SADecline          |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | SANonCand          |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | SAScreen           |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | Sealant            |                  414 |         0 |         0 |         0 |         0 |         0 |         0 |       150 |       103 |       101 |         49 |         11 |          0 |
|       6 | SealantRedo        |                   49 |         0 |         0 |         0 |         0 |         0 |         0 |         4 |        11 |        19 |         12 |          3 |          0 |
|       6 | test               |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
|       6 | Veneer             |                    2 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |          1 |          0 |          0 |
|       6 | WFL                |                  111 |         0 |         0 |         0 |         0 |         0 |         0 |        35 |        22 |        20 |         25 |          9 |          0 |
|       6 | White              |                    0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |          0 |          0 |          0 |
+---------+--------------------+----------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+

Open in new window


----

I did purge quite a bit of data.  I removed everything prior to 7/1/18 and for `site_id`!=6.  The actual transaction_data table is nearly 1GB in size w/ over 3 mil records.  I wanted to leave enough data to be able to get a good representation.