I am trying to combine two different queries that are basically serving the same function. The results of each query are being categorized based upon the results of one column. When I have tried to combine them, I receive an error stating there is an arithmetic overflow.
Below is the first line of code.
select t1.Lives_Category,
Count(t3.clean_ft_ee_cnt) "MVP Number of Clients",
sum(t3.clean_ft_ee_cnt) as "MVP Sum of Lives"
from [mvpGA].[mvp_client_data_t] t3
join
(
select * from
(values ('1-9 Lives',1,9),
('10-49 Lives',10,49),
('50-199 Lives',50,199),
('200-499 Lives',200,499),
('500-1,9999 Lives',500,1999),
('2,000+ Lives',2000,100000000),
('Total Lives',0,100000000),
('500+ Lives',500,1000000000),
('Unknown Lives',0,0)
)
as Base_Table (Lives_Category,MinCnt,MaxCnt)
) t1
on t3.clean_ft_ee_cnt between t1.MinCnt and t1.MaxCnt
where t3.mvp_rpt_id > 1399
and t3.clean_do_not_use_ind is NULL
and t3.clean_client_indv_flag = 'Group'
group by t1.Lives_Category
the results then show as:
Lives_Category | MVP Number of Clients | MVP Sum of Lives
-----------------------------------------------------------
1-9 Lives | 7565 | 33845
10-49 Lives | 7996 | 191190
50-199 Lives | 6820 | 680157
200-499 Lives | 2281 | 683971
500-1,9999 Lives | 1510 | 1424911
2,000+ Lives | 672 | 8282279
| |
Total Lives | 26929 | 11296353
| |
500+ Lives | 2182 | 9707190
| |
Unknown Lives | 85 | 0
Second line of code is here and is the same structure, the difference is the where clause.The results of the two columns "Number of Clients" and "Sum of Live" will need to be their own columns.
select distinct a1.Lives_Category as "Lives Category",
count(t2.clean_ft_ee_cnt) as "Number of Clients",
sum(t2.clean_ft_ee_cnt) as "Sum of Lives"
from [mvpGA].[mvp_client_data_t] t2
join
(
select * from
(values ('1-9 Lives',1,9),
('10-49 Lives',10,49),
('50-199 Lives',50,199),
('200-499 Lives',200,499),
('500-1,9999 Lives',500,1999),
('2,000+ Lives',2000,100000000),
('Total Lives',0,100000000),
('500+ Lives',500,1000000000),
('Unknown Lives',0,0)
)
as Base_Table (Lives_Category,MinCnt,MaxCnt)
) a1
on t2.clean_ft_ee_cnt between a1.MinCnt and a1.MaxCnt
where t2.mvp_rpt_id = 1400
and t2.clean_mvp_rpt_breakout = 'Fargo'
and t2.clean_do_not_use_ind is NULL
and t2.clean_client_indv_flag = 'Group'
group by a1.Lives_Category
I would like the end result to be 5 columns. See structure below.
Lives_Category | MVP Number of Clients | MVP Sum of Lives | Number of Clients | Sum of Lives
So to recap, the results are categorized the same as shown in the Lives_Category.....but I would like to have 5 columns. Note: the queries are technically calculating off of the same table, there are just different where clauses.
I do not know enough about your data to know if you should use a inner, left, right, or full join. So you should modify the join to suit your situation, but this should get you on the right track. Basically, I put each your your select statement in a subquery and join on the common field, "Lives Category".
SELECT COALESCE(x.[Lives Category], y.[Lives Category]) AS [Lives Category]
, x.[MVP Number of Clients]
, x.[MVP Sum of Lives]
, y.[Number of Clients]
, y.[Sum of Lives]
FROM (
SELECT t1.Lives_Category AS [Lives Category]
, COUNT(t3.clean_ft_ee_cnt) AS [MVP Number of Clients]
, SUM(t3.clean_ft_ee_cnt) AS [MVP Sum of Lives]
FROM mvpGA.mvp_client_data_t AS t3
JOIN (
SELECT *
FROM (
VALUES ('1-9 Lives', 1, 9)
, ('10-49 Lives', 10, 49)
, ('50-199 Lives', 50, 199)
, ('200-499 Lives', 200, 499)
, ('500-1,9999 Lives', 500, 1999)
, ('2,000+ Lives', 2000, 100000000)
, ('Total Lives', 0, 100000000)
, ('500+ Lives', 500, 1000000000)
, ('Unknown Lives', 0, 0)
) AS Base_Table (Lives_Category, MinCnt, MaxCnt)
) AS t1 ON t3.clean_ft_ee_cnt BETWEEN t1.MinCnt AND t1.MaxCnt
WHERE t3.mvp_rpt_id > 1399
AND t3.clean_do_not_use_ind IS NULL
AND t3.clean_client_indv_flag = 'Group'
GROUP BY t1.Lives_Category
) AS x
FULL JOIN (
SELECT DISTINCT
a1.Lives_Category AS [Lives Category]
, COUNT(t2.clean_ft_ee_cnt) AS [Number of Clients]
, SUM(t2.clean_ft_ee_cnt) AS [Sum of Lives]
FROM mvpGA.mvp_client_data_t AS t2
JOIN (
SELECT *
FROM (
VALUES ('1-9 Lives', 1, 9)
, ('10-49 Lives', 10, 49)
, ('50-199 Lives', 50, 199)
, ('200-499 Lives', 200, 499)
, ('500-1,9999 Lives', 500, 1999)
, ('2,000+ Lives', 2000, 100000000)
, ('Total Lives', 0, 100000000)
, ('500+ Lives', 500, 1000000000)
, ('Unknown Lives', 0, 0)
) AS Base_Table (Lives_Category, MinCnt, MaxCnt)
) AS a1 ON t2.clean_ft_ee_cnt BETWEEN a1.MinCnt AND a1.MaxCnt
WHERE t2.mvp_rpt_id = 1400
AND t2.clean_mvp_rpt_breakout = 'Fargo'
AND t2.clean_do_not_use_ind IS NULL
AND t2.clean_client_indv_flag = 'Group'
GROUP BY a1.Lives_Category
) AS y ON x.[Lives Category] = y.[Lives Category];