asked on

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.

Dustin Saunders

You could use a case statement to accomplish that.

Here are my 2 tables.

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

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.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

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

Ryan Chong

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

Ryan Chong

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.

Pawan Kumar

Provided tested solution.
Other comments are not working fine.