October 7, 2007 at 4:06 am
I want to display three different counts in a single row. Any clues on the select statement.
Must look something like this:
-------------------------------------------------------------------
Facility No. 1
Records for Cond. 1 Records for Cond. 2 Records for Cond. 3
-------------------------------------------------------------------
-------------------------------------------------------------------
Facility No. 2
Records for Cond. 1 Records for Cond. 2 Records for Cond. 3
-------------------------------------------------------------------
So I get three different counts at different conditions in a row under certain facility.
Also the facility number must appear just above the counts, not a separte line. Hope it explains.
Regards
October 7, 2007 at 8:43 am
how about :
select sum(case when condition1 then 1 else 0 end ) as whatevernameyouwannegiveit
, sum(case when condition2 then 1 else 0 end ) as whatevernameyouwannegiveit_theSecond
from yourobject
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 7, 2007 at 9:10 pm
I'd use ALZDBA's solution.
If that doesn't make sense, give us an idea of what the conditions are and we can help (tables/cols)
October 8, 2007 at 12:25 am
Thank you ALZDBA
Actually your solution is similar to
select Facility1 as [Facility 1]
,(select count(*) FROM MyTable WHERE Cond1) as [First Condition]
,(select count(*) FROM MyTable WHERE Cond2) as [Second Condition]
,(select count(*) FROM MyTable WHERE Cond3) as [Third Condition]
Now what about the 2nd row, how do I use carriage return and start the 2nd line for Facility2. I don't want to use a separate select statement for the 2nd Facility, so that my result appear like a table.
October 8, 2007 at 12:33 am
Normaly if you want separate rows, perform separate selects or take things in your own hands(meaning you perform positioning yourself at front end)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 8, 2007 at 6:43 am
To elaborate on ALZDBA's solution a little to make it clear what he was proposing:
select facilityname
, sum(case when condition1 then 1 else 0 end ) as whatevernameyouwannegiveit
, sum(case when condition2 then 1 else 0 end ) as whatevernameyouwannegiveit_theSecond
, sum(case when condition2 then 1 else 0 end ) as whatevernameyouwannegiveit_theThird
from yourobject
group by facilityname
If you compare the execution plan for this and your proposed subquery solution, you will see the huge difference is that this only scans the table once.
October 8, 2007 at 7:36 am
ckmoied (10/8/2007)
Thank you ALZDBAActually your solution is similar to
select Facility1 as [Facility 1]
,(select count(*) FROM MyTable WHERE Cond1) as [First Condition]
,(select count(*) FROM MyTable WHERE Cond2) as [Second Condition]
,(select count(*) FROM MyTable WHERE Cond3) as [Third Condition]
Now what about the 2nd row, how do I use carriage return and start the 2nd line for Facility2. I don't want to use a separate select statement for the 2nd Facility, so that my result appear like a table.
Remember, SQL is meant for returning a resultset and the UI layer is meant for formatting and presentation. I am sure there is at least 1 member of SSC that could provide with a T-SQL solution for your question, but SQL Is not the best tool for doing presentation and formatting. Pretty much any solution you will get with SQL Server will return a result like:
Facility Condition1 Condition2 Condition3
------- ---------- ---------- ----------
Facility1 10 12 14
Facility2 7 18 22
You would then use your UI to format as you like. IF you have SQL 2000 or later you can use SSRS which easily allows what you want, as would Crystal Reports, or some other report writer.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2007 at 8:03 am
Building in formatting, like carriage returns, is a horrible use of SQL Server resources. You're asking for a lot of work from a limited, shared resource. As mentioned above, whatever is actually displaying this for the user should do the formatting.
October 9, 2007 at 1:47 am
Thanks for support by all,
Actually I am supposed to carry out testing of data integration for my client, and have to show them results in interprise manager or Query Analyzer first and then counter check in the interfaces. The data is scattered in lots of tables and I want to show the records in one screen rather than a huge list of scrollable records.
I tried to used char(13) and char(10) sort of things but they didn't work.
October 9, 2007 at 10:51 am
I agree with everyone about using SQL to do formatting, but this will work
DROP TABLE #Doit
CREATE TABLE #Doit
(a INT IDENTITY,z INT)
go
INSERT INTO #Doit(z)
VALUES(NULL)
GO 50
SELECT '------------------
',a,'
-----------------'
FROM #Doit
October 9, 2007 at 11:46 am
As everyone else has said formatting is best left to reporting tools but this will do what you want.
declare @delim1 varchar(30),@delim2 varchar(30),@delim3 varchar(30)
select @delim1 = '---------------'+ char(10) + char(13)
select @delim2 = char(10) + char(13)
select @delim3 = ','
select @delim1,facility,@delim2,sum(case when something = somethingelse then 1 else 0 end) as con1,@delim3,sum(case when something = somethingelse then 1 else 0 end) as con2,@delim3,sum(case when something = somethingelse then 1 else 0 end) as con3,@delim2,@delim1 from #temp
group by facility
October 14, 2010 at 9:50 am
ALZDBA's solution works perfectly... (atleast for me)
select Facility,
sum(case when condition_1 then 1 else 0 end),
sum(case when condition2 then 1 else 0 end)
from Table
group by Facility
Solution would look like this:
Facility_1 Count(for_condition_1) Count(for_condition_2) ......
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply