SQL Group by Default Setting.

I have 2 tables.  
CC
 ID, ItemID, Group
1, Mt123, A
2, Mt235, B
3, Mt556, B

CCDays
id, Group, Days
1,   A,       22
2, DEF,    90


What I need is.  If the Group in CC is not in CCDays to use the DEF Group instead.     So we I figure out the number of items that need to be counted based on group I get DEF count group for all that are not in the CCDays group.

SELECT        COUNT(*) AS totItems, CCDays.CountDays, CEILING(COUNT(*) / CAST(CCDays.CountDays AS Decimal)) AS itemsCount, CCDays.ItemGroup
FROM          CC LEFT OUTER JOIN
                        CCDays ON cc.ItemGroup = CCDays.ItemGroup
GROUP BY CCDays.ItemGroup, CCDays.CountDays

What I get.
totItems  CountDays      itemsCount  ItemGroup
209               22                           10                 A
5910      NULL                  NULL               NULL

What I need
totItems  CountDays      itemsCount  ItemGroup
209               22                           10                 A
5910      90                        67               DEF


Thanks.
LVL 4
EdwardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Dustin SaundersDirector of OperationsCommented:
You could use a case statement to accomplish that.

Here are my 2 tables.
table1.pngtable2.png
Query
SELECT t1.id,t1.itemid,t1.[group],t2.[days]
FROM Table_1 t1
LEFT JOIN Table_2 t2 on (CASE WHEN t1.[group] IN (SELECT [group] FROM Table_2) THEN t1.[group] ELSE 'DEF' END) = t2.[group]

Open in new window


Results
results.png
There may be a more efficient way to do it, in which case another expert may chime in but you should be able to do your joins conditionally this way.
0
Pawan KumarDatabase ExpertCommented:
This should help you. You need to change the column Names.

SELECT   COUNT(*) AS totItems,[Group],CountDays
		,CAST(COUNT(*) * 1.00 / ISNULL(CountDays,SUM(CASE WHEN [Group] = 'DEF' THEN CountDays ELSE 0 END) OVER()) AS DECIMAL(10,2)) itemsCount
FROM 
(
	SELECT cc.*,ISNULL(CCDays.Days,ISNULL(CCDays.Days,SUM(CASE WHEN CCDays.[Group] = 'DEF' THEN [Days] ELSE 0 END) OVER())) CountDays
	FROM  CC FULL OUTER JOIN
	CCDays ON cc.[Group] = CCDays.[Group]
)o
WHERE Id IS NOT NULL
GROUP BY [Group],CountDays

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
totItems    Group CountDays   itemsCount
----------- ----- ----------- ---------------------------------------
1           A     22          0.05
2           B     90          0.02

(2 row(s) affected)

Open in new window

0

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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
or if you want to count the no of records that are not related in your relationship. you may try if this is what you want?

SELECT COUNT(*) AS totItems, CCDays.CountDays,
CEILING(COUNT(*) / CAST(CCDays.CountDays AS Decimal)) AS itemsCount,
CCDays.ItemGroup
FROM CCDays LEFT OUTER JOIN
CC ON CCDays.ItemGroup = cc.ItemGroup 
GROUP BY CCDays.ItemGroup, CCDays.CountDays

Open in new window


that's to make CCDays your main table to left join with CC
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
OR change the LEFT JOIN in your original SQL to RIGHT JOIN.

SELECT        COUNT(*) AS totItems, CCDays.CountDays, CEILING(COUNT(*) / CAST(CCDays.CountDays AS Decimal)) AS itemsCount, CCDays.ItemGroup
FROM    CC RIGHT OUTER JOIN
             CCDays ON cc.ItemGroup = CCDays.ItemGroup
GROUP BY CCDays.ItemGroup, CCDays.CountDays

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I guess Pawan's solution may still have room for improvement as it fixed the value [Group] = 'DEF' in the statement. hence it will only handle for 1 case condition. but no doubt to say, that's depends on user's requirement if that's enough to handle the scenario.

@edwardq
let's us know if you need further assistance here. and do evaluate other suggestions and see if that worked exactly for your requirement.
0
Pawan KumarDatabase ExpertCommented:
Provided tested solution.
Other comments are not working fine.
0
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.