Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2014-02-19 : 08:42:07
|
Hey all I need some help, Aim- Need to count how many [FDMSAccountNo] fall under a unique parentidThis is my query SELECT [FDMSAccountNo] ,ParentID FROM [FDMS].[dbo].[Dim_Outlet] where ParentID = '878028271889'Which produces the following,The number of fdmsaccountno under parentid is two. Its two because one of the fdmsaccountno is = to parentidFDMSAccountNo ParentID878028271889 878028271889878028272887 878028271889878028273885 878028271889Desired result Parentid #_of_outlets878028271889 2Looking forward to your help |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-19 : 09:19:49
|
SELECT ParentID, COUNT(DISTINCT NULLIF(FDMSAccountNo, ParentID)) AS [#_of_outlets] FROM dbo.Dim_Outlet GROUP BY ParentID; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-20 : 00:34:15
|
[code]SELECT COUNT([FDMSAccountNo]) AS NoOfOutlets,ParentIDFROM [FDMS].[dbo].[Dim_Outlet]where ParentID <> [FDMSAccountNo][/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-22 : 09:31:27
|
Visakh, that will omit ParentID which has only Accountnumber equal to ParentID, and the ParentID will be missing completely from the list. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-23 : 02:44:26
|
ok. if you want to include that too just tweak it asSELECT COUNT(CASE WHEN ParentID <> [FDMSAccountNo] THEN [FDMSAccountNo] ELSE NULL END) AS NoOfOutlets,ParentIDFROM [FDMS].[dbo].[Dim_Outlet]GROUP BY ParentID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-24 : 12:34:06
|
And that is the same as NULLIF, right? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|