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
edwardqAsked:
Who is Participating?
 
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
 
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
 
Ryan ChongCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Ryan ChongCommented:
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 ChongCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.