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.
LVL 1
bdhtechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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.
PortletPaulEE Topic AdvisorCommented:
>>"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)
bdhtechnologyAuthor Commented:
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

10 Holiday Gifts Perfect for Your Favorite Geeks

Still have some holiday shopping to do for the geeks in your life? While toys, clothing, games, and gift cards are still viable options for your friends and family, there’s more reason than ever to consider gadgets and software.

PortletPaulEE Topic AdvisorCommented:
I am really not sure that you need to place you first query into the second query to fix this

I was going to suggest this:
SELECT *
FROM code_map b 
LEFT JOIN (
        SELECT 
              service_code
            , site_id
            , COUNT(*) AS `trans_ttl`, 
            , SUM( IF(tran_date >= '2018-01-01' AND tran_date < '2018-02-01', 1, 0)) AS `interval1`
        	, SUM( IF(tran_date >= '2018-02-01' AND tran_date < '2018-03-01', 1, 0)) AS `interval2`
        	, SUM( IF(tran_date >= '2018-03-01' AND tran_date < '2018-04-01', 1, 0)) AS `interval3`
        	, SUM( IF(tran_date >= '2018-04-01' AND tran_date < '2018-05-01', 1, 0)) AS `interval4`
        	, SUM( IF(tran_date >= '2018-05-01' AND tran_date < '2018-06-01', 1, 0)) AS `interval5`
        	, SUM( IF(tran_date >= '2018-06-01' AND tran_date < '2018-07-01', 1, 0)) AS `interval6`
        	, SUM( IF(tran_date >= '2018-07-01' AND tran_date < '2018-08-01', 1, 0)) AS `interval7`
        	, SUM( IF(tran_date >= '2018-08-01' AND tran_date < '2018-09-01', 1, 0)) AS `interval8`
        	, SUM( IF(tran_date >= '2018-09-01' AND tran_date < '2018-10-01', 1, 0)) AS `interval9`
        	, SUM( IF(tran_date >= '2018-10-01' AND tran_date < '2018-11-01', 1, 0)) AS `interval10`
        	, SUM( IF(tran_date >= '2018-11-01' AND tran_date < '2018-12-01', 1, 0)) AS `interval11`
        	, SUM( IF(tran_date >= '2018-12-01' AND tran_date < '2018-01-01', 1, 0)) AS `interval12`
        FROM transaction_data
        WHERE site_id = 6 
        AND tran_date >= '2018-01-01' AND tran_date < '2018-01-01'
        AND impacts = 'P' 
        AND type = 'S' 
        AND status = 'A' 
        GROUP BY
              service_code
            , site_id
    ) a ON b.service_code=a.service_code AND b.site_id = a.site_id
WHERE b.site_id = 6  
ORDER BY b.site_id, b.service_category
;

Open in new window


1. I worry that you might be missing data because you are using between. The MUCH SAFER way to define date ranges to to AVOID using between.

2. I can see no reason for the second group by clause, there are no aggregations in the outer query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
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.
bdhtechnologyAuthor Commented:
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
bdhtechnologyAuthor Commented:
@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`
PortletPaulEE Topic AdvisorCommented:
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.
bdhtechnologyAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.