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 |
|
Kroni
Starting Member
4 Posts |
 Posted - 2013-07-29 : 08:31:03
|
| Hello Everyone!this is my first post here. I have certain issues with sql code i have to modifySELECT status, COUNT(status) AS #machines, MIN(recorddate) AS startdate FROM citrix WHERE pool='POOL6668' recorddate BETWEEN '2013-07-18' AND '2013-07-18 22:00' GROUP BY status ORDER BY status, startdate;Im using this inside a VBL script to create a Excel sheet. Now my Problem is with the status part. There are 4 choices i could get back "used,unknown, ready or accessdenied"Now i want to extract those 4 status into an excel sheet into a graph. BUT i want to extract two together into one colum (so i want to put unknown and accessdenied together), so instead of 4 status choices i want only 3 to appear on my graph (1,2+4,3)so how do i put these together into a nice SQL command? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-07-29 : 08:40:20
|
| SELECT CASE WHEN status IN ('unknown', 'accessdenied') THEN 'BothTogether' ELSE status END CustomisedStatus ,COUNT(status) AS #machines, MIN(recorddate) AS startdate FROM citrixcWHERE pool='POOL6668' recorddate BETWEEN '2013-07-18' AND '2013-07-18 22:00'GROUP BY CASE WHEN status IN ('unknown', 'accessdenied') THEN 'BothTogether' ELSE status END ORDER BY status, startdate;--Chandu |
 |
|
|
Kroni
Starting Member
4 Posts |
Posted - 2013-07-29 : 08:53:39
|
| there is still a mistake there. i think it is that when i do COUNT(status) and i get BothTogether instead, he might not be happy with that.or Code: 8127, SQL State: S1000] Column "citrix.status" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-29 : 09:04:58
|
To fix the order by problem, make the change to Chandu's query as shown belowORDER BY CustomisedStatus, startdate; Regarding the counts, if you have aggregated the four statuses into 3 custom statuses, doesn't it make sense that you get only 3 rows, and so one count for each row. How shold the 3 statuses and four counts be returned if indeed you want to get 4 counts? |
|
|
|
Kroni
Starting Member
4 Posts |
Posted - 2013-07-29 : 09:18:51
|
| well i want to get back 3 counts indeed and yea 3 rows because im basically turning 2 counts into one (the unknown and accessdenied) thus also just having 3 rows.the bothtogether row, the used, and the ready rownot really sure what your question is, but im starting to confuse myself by now too. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-29 : 09:33:13
|
My question was about what you said here: quote:
there is still a mistake there. i think it is that when i do COUNT(status) and i get BothTogether instead, he might not be happy with that.
If Chandu's query does not give you what you are looking for, if you can post a sample of the exact output that you want to get, someone should be able to modify the query to meet your requirements. |
|
|
|
Kroni
Starting Member
4 Posts |
Posted - 2013-07-29 : 10:00:04
|
| I feel like the querry is good now, but i need further testing next part is to modify the VB-script to give me the excel output i want.regards |
|
|
|
|
|
|
|
|
Help with joining status