Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

SWQL: combine 3 Colum data count

Need help to combine 3 columns data count

Like :

Media type	Total	ATM Branches	Non-ATM Branch
Fiber	300	150	50

Mentioned SWQL query showing output in different format.

SELECT COUNT(n.DisplayName) as Total_Cybernet_fiber,

n.CustomProperties.Branch_Media as Location,
n.CustomProperties.Media_Type_1 as M1,
n.CustomProperties.Media_Type_2 as M2,
n.CustomProperties.Media_Type_3 as M3

FROM Orion.Nodes n

WHERE ( n.CustomProperties.SP1 LIKE 'cybernet%' or n.CustomProperties.SP2 LIKE 'cybernet%' or n.CustomProperties.SP3 LIKE 'cybernet%' )

--and ( n.CustomProperties.Media_Type_1 Like 'fiber%' or n.CustomProperties.Media_Type_2 Like 'fiber%' or n.CustomProperties.Media_Type_3 Like 'fiber%')

GROUP BY n.CustomProperties.Branch_Media , n.CustomProperties.Media_Type_1 , n.CustomProperties.Media_Type_2 , n.CustomProperties.Media_Type_3

Find more posts tagged with

Accepted answers

All comments

makecrazy

can any one provide help on above mentioned query

mesverrum

Seems like a really messy set of data, but if you want exactly the format you described at the top then this should work or be close

SELECT SUM(case when n.CustomProperties.Media_Type_1 = 'fiber' or n.CustomProperties.Media_Type_2 = 'fiber' or n.CustomProperties.Media_Type_3 = 'fiber' then 1 else 0) as Total_Cybernet_fiber,

n.CustomProperties.Branch_Media as Location

FROM Orion.Nodes n

WHERE ( n.CustomProperties.SP1 LIKE 'cybernet%' or n.CustomProperties.SP2 LIKE 'cybernet%' or n.CustomProperties.SP3 LIKE 'cybernet%' )

and ( n.CustomProperties.Media_Type_1 Like 'fiber%' or n.CustomProperties.Media_Type_2 Like 'fiber%' or n.CustomProperties.Media_Type_3 Like 'fiber%')

GROUP BY n.CustomProperties.Branch_Media

makecrazy

got simple query for same but need to get result in this format

Media type	Total	ATM Branches	Non-ATM Branch
Fiber	300	150	50

SELECT 

n.CustomProperties.Branch_Media as Location,
COUNT(n.NodeID) AS [Total Fiber]

FROM Orion.Nodes n


WHERE ( n.CustomProperties.SP1 LIKE 'cybernet%' or n.CustomProperties.SP2 LIKE 'cybernet%' or n.CustomProperties.SP3 LIKE 'cybernet%' )

and ( n.CustomProperties.Media_Type_1 Like 'fiber%' or n.CustomProperties.Media_Type_2 Like 'fiber%' or n.CustomProperties.Media_Type_3 Like 'fiber%')

GROUP BY n.CustomProperties.Branch_Media

adam.beedell

Select concat(c.[MediaType], ', ' , c.[Total], ', ' , c.[Atm Branches], ', ' , c.[on-ATM Branch]) as smushedtogether

FROM
(
previous query here
) as c