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:
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:
code_map structure:
transaction_data structure:
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 ;
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 ;
code_map structure:
CREATE TABLE `code_map` (
`id` int(5) NOT NULL COMMENT 'row_id',
`client_id` int(5) NOT NULL,
`site_id` int(10) NOT NULL,
`service_category` varchar(30) NOT NULL COMMENT 'service category',
`service_code` varchar(30) NOT NULL COMMENT 'service_code'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
transaction_data structure:
CREATE TABLE `transaction_data` (
`site_id` int(5) NOT NULL COMMENT 'site id number',
`tran_num` int(10) NOT NULL COMMENT 'local transaction number',
`status` varchar(30) NOT NULL,
`fee` decimal(8,2) NOT NULL,
`amount` decimal(8,2) NOT NULL COMMENT 'dollar amount of transaction',
`service_code` varchar(20) NOT NULL COMMENT 'service code from ppm',
`tran_date` datetime NOT NULL,
`impacts` varchar(30) NOT NULL,
`type` varchar(30) NOT NULL,
`provider_id` varchar(9) DEFAULT NULL,
`provider` varchar(100) NOT NULL,
`provider_position` varchar(50) NOT NULL,
`pay_type` varchar(45) NOT NULL,
`patient_id` varchar(40) DEFAULT NULL,
`adjustment_type` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY HASH (site_id)
PARTITIONS 20;
MySQL ServerSQL
Last Comment
bdhtechnology
>>"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)
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)
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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 ;
ASKER
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.
transaction_data.sql
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
transaction_data.sql
ASKER
@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)
----
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.
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
;
+----+------+-----------+---------+--------------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
| | 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 |
+----+------+-----------+---------+--------------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
----
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.
ASKER
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:
----
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.
+---------+--------------------+----------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
| 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 |
+---------+--------------------+----------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------------+------------+------------+
----
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.
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.