January 23, 2019 at 4:30 am
Hi All, Need help.
I have a table which has computers and their related OU. I need to get the total count of computers related to set of OU.
EG:
| OU | Total computers |
| 100100F | 100 |
| 100100FI | 50 |
| 100100FD | 10 |
| 200100F | 200 |
| 200100FI | 20 |
| 200100FD | 15 |
| 200200F | 250 |
| 200200FI | 9 |
| 200200FD | 20 |
output should be:
| OU | Total computers |
| 100100F | 160 |
| 200100F | 235 |
| 200200F | 279 |
How to get this?
Thanks in advance.
January 23, 2019 at 5:04 am
Just looking at the data - if the first 7 digits are always going to be same - you can GROUP BY LEFT(OU , 7) - if that's not the case - I guess creating a lookup table for the grouping values and joining it to this table will do..
January 23, 2019 at 5:57 am
@Taps - Wednesday, January 23, 2019 5:04 AMJust looking at the data - if the first 7 digits are always going to be same - you can GROUP BY LEFT(OU , 7) - if that's not the case - I guess creating a lookup table for the grouping values and joining it to this table will do..
I go for the latter and use a lookup table, one never knows when the requirements WILL change.
😎
January 24, 2019 at 1:43 am
If i use group by left(OU, 7) am getting the desired output only in terms of total count. what should i do to get both OU and total count as shown below?
| OU | Total computers |
| 100100F | 160 |
| 200100F | 235 |
| 200200F | 279 |
output should have both column OU and Total computers. how to achieve that?
January 24, 2019 at 3:49 am
Helpseeker - Thursday, January 24, 2019 1:43 AMIf i use group by left(OU, 7) am getting the desired output only in terms of total count. what should i do to get both OU and total count as shown below?
OU Total computers 100100F 160 200100F 235 200200F 279 output should have both column OU and Total computers. how to achieve that?
;WITH CTE AS
(SELECT * FROM (VALUES
('100100F', 100),
('100100FI', 50),
('100100FD', 10),
('200100F', 200),
('200100FI', 20),
('200100FD', 15),
('200200F', 250),
('200200FI', 9),
('200200FD', 20)) T(OU, [Total computers])
)
SELECT LEFT(OU,7) OU,
SUM([Total computers]) [Total computers]
FROM CTE
GROUP BY LEFT(OU,7)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply