Aggregate - Group by ?
-
Sample data
Area Codes 1=Red 2=Yellow 3=Green
Customer server Area1 area2 Area3 Area4
BOA S1 1 1 1 2
BOA S2 2 2 3 3
BOA S3 3 3 3 3
Jcrew H1 3 3 3 3
Jcrew H2 1 1 2 2
Sony M1 3 3 3 3
Business rules:
If a server has one or more red areas, server is marked red
If server has one or more yellow areas, server is marked yellow
If server has all areas green, server is marked green
If customer has one or more red servers, customer is marked red
If customer has one or more yellow servers, customer is marked yellow
If customer has all servers green, customer is marked green
Thus, sample table figure 1
Cust |Server |Server 1-count| server 2-count |server 3-count |serverOverall
BOA S1 3 1 0 1
BOA S2 0 2 2 2
BOA S3 0 0 3 3
Jcrew H1 0 0 3 3
Jcrew H2 2 2 0 2
Sony M1 3 3 3 3
Desired Results
Cust | #Servers | # Red Servers | # Yellow Serv | #Green Servers
BOA 3 1 1 1
Jcrew 2 0 1 1
Sony 1 0 0 0
I can write T-sql to arrive to sample table figure 1 but I am not sure how to approach to arrive at the desired results. I don't want to have to use cursors and the group by gives me only the first two columns in the desired result table.
Any suggestions will be much appreciated and thank you in advance.
-
what you want is a pivot or crosstab query and there are lots of post about dynamically creating them on the forum.
The static version for this task looks like this:
select cust,count(server),
sum(case when [Server 1-count] >1,1 else 0 end) as red,
sum(case when [Server 2-count] >1,1 else 0 end) as yellow,
sum(case when [Server 3-count] >1,1 else 0 end) as green
from [sample table figure 1]
group by cust
-
Okay - I give
. I'm not understanding how you're counting, because your sample numbers aren't making sense relating to your sample data.Caffeine will be required soon.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
It is a pity that your original table (I mean the one at the very beginning) isn't a bit more normalized... if you'd have columns Area_name and Area_status instead of these Area1, Area2 etc., it would be much easier to handle the data. But maybe you posted a table because it's visually better, and in fact the data are stored differently?
BTW, in your desired result, I suppose Sony should have one green server, and Jcrew 1 red and 1 green (no yellow).
Please post your table definitions if you still need help... This is how it would look with normalized data:
/*test table*/
CREATE TABLE #server_table(customer varchar(10), server varchar(10), area_name varchar(10), area_status INT)
/*data according to your specification*/
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S1','A1',1)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S1','A2',1)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S1','A3',1)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S1','A4',2)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S2','A1',2)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S2','A2',2)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S2','A3',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S2','A4',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S3','A1',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S3','A2',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S3','A3',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('BOA','S3','A4',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H1','A1',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H1','A2',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H1','A3',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H1','A4',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H2','A1',1)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H2','A1',1)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H2','A1',2)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Jcrew','H2','A1',2)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Sony','M1','A1',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Sony','M1','A2',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Sony','M1','A3',3)
INSERT INTO #server_table(customer, server, area_name, area_status) VALUES ('Sony','M1','A4',3)
/*result table*/
SELECT servers.customer, COUNT(*) as total,
SUM(CASE WHEN servers.srvstat=1 THEN 1 ELSE 0 END) as red,
SUM(CASE WHEN servers.srvstat=2 THEN 1 ELSE 0 END) as yellow,
SUM(CASE WHEN servers.srvstat=3 THEN 1 ELSE 0 END) as green,
MIN(servers.srvstat) as cust_overall_code
FROM
(select customer, server, MIN(area_status) as srvstat
from #server_table
group by customer, server) as servers
GROUP BY customer
/*cleanup*/
DROP TABLE #server_table
-
Thank you both mrpolecat and Vladan.
What you share with me works.
It is unfortunate that the "database" is not normalized. I am working on a web interface for a group of Unix/Windows administrators whose job is to run audits on their servers. They capture the output from the audit runs and store it in Sql server 2005. So it is more a data store than a database because everything is flat.
If I normalize the database, they will have to change their vbs scripts. They have no time to do so and won't let me touch their vbs scripts.
So I just have to work with what exists and learn to manipulate stored data.
-
Yeah, that's the real life 🙂 you have to work with what you've got.
Well, did you manage to get at the required result from what polecat posted? If not, let us know where is the problem and we will try again.
Good luck!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply