Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiple Counts in a Row Expand / Collapse
Author
Message
Posted Sunday, October 7, 2007 4:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 8, 2009 1:53 PM
Points: 56, 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
Post #407772
Posted Sunday, October 7, 2007 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:42 AM
Points: 6,743, Visits: 8,515
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #407779
Posted Sunday, October 7, 2007 9:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:53 PM
Points: 31,279, Visits: 15,739
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
Post #407841
Posted Monday, October 8, 2007 12:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 8, 2009 1:53 PM
Points: 56, 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.

Post #407870
Posted Monday, October 8, 2007 12:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:42 AM
Points: 6,743, Visits: 8,515
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #407871
Posted Monday, October 8, 2007 6:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
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.



Post #407950
Posted Monday, October 8, 2007 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
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

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
Post #407982
Posted Monday, October 8, 2007 8:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:53 PM
Points: 31,279, Visits: 15,739
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
Post #408002
Posted Tuesday, October 9, 2007 1:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 8, 2009 1:53 PM
Points: 56, 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.
Post #408321
Posted Tuesday, October 9, 2007 10:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, 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

Post #408592
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse