SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Counts in a Row


Multiple Counts in a Row

Author
Message
ckmoied
ckmoied
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 149
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
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12009 Visits: 8918
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61503 Visits: 19097
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)

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ckmoied
ckmoied
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 149
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.
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12009 Visits: 8918
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3786 Visits: 1463
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.



Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18286 Visits: 14889
ckmoied (10/8/2007)
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.









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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61503 Visits: 19097
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ckmoied
ckmoied
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 149
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.
Bob Fazio
Bob Fazio
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1334 Visits: 683
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search