|
I have a table name victim in which there is a field called "Gender". It has three values 1.Male
2.Female
3.Child
What I want is to write a query that will count all entries and the total of all entries.
Something like below
Male Female Child Total
1 2 0 3
0 4 3 7
Can any 1 help me please on how to write this query?
|
|
|
|
|
why there is two rows? is this result of group by?
BTW I think the simplest way could be declaring function that take some argument and return your desired result.
i don't know the logic but if you have some unique fields at least in group by sets you can also use derived queries(or views)
I Wish the Life Had CTRL-Z
Wizard's First Rule : People are fool,they believe what they want to believe or what they afraid to believe
www.subaitech.blogspot.com
|
|
|
|
|
Select count(*) Gender from Table Group By Gender
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi.
I think sub select is the simplest way:
select (Select count(*) from Table where Gender = 1) as MaleCount, (Select count(*) from Table where Gender = 2) as FemaleCount, (Select count(*) from Table where Gender = 3) as ChildCount, count(*) as TotalCount from Table
Ugly but working...
Kjetil
|
|
|
|
|
Hi all
I'm trying to put a condition on a the background color for a matrix subtotal and for some reason it's not working, it always assign the same color for the rest the columns to be the same. It seems that it check fro the first column condition and then assign that color.
Any help will be appreciated
Thanks
T
|
|
|
|
|
I have three different tables in a SQL DB which have at least two common fields amongst each other.
Should I just create a new table that is made of these common fields and have these three tables point to it for those fields?
|
|
|
|
|
I don't think there is a right or wrong answer to this question. It depends on the data. If the data is vital and it's very important that it's always updated and the same across the three files, you may want to separate and make a fourth table out of it so you only have to update in one location. However, if it's not that vital of information or it very rarely changes you may want to keep it in each file because it's easier to access. Hope this helps.
|
|
|
|
|
Depending on your domain model you could approach this as a reference, or an inheritance relationship. Pick the most appropriate.
Reference is say pulling out "Address1, Address2, Address3" into a Address table, and have Customer.Address, Supplier.Address -> Address.ID with a fk relationship.
Inheritance is where you take out the Address1-3 columns into a Person table, and have Customer.Id -> Person.ID, and Supplier.ID -> Person.ID. (So every record in supplier or customer must have a record in person - this maps directly to inheritance in your business layer, which can be handy).
|
|
|
|
|
Read up on Normalisation - this probably going to be the root issue. The common fields in the table should ONLY be IDs, if you are updating data in 3 different tables then your design is wrong.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Where can I found a x64 version from the SQLAGENT?
In the WebPage have a x86 version....
Thanks!
Rafelq2000
|
|
|
|
|
hi
i am created a store procedure like this for select top list user definlly
create procedure ss(@s int)
as
select top @s sno from summa1 order by newid()
it can not create and display error
how to create
with regards,
bretto
|
|
|
|
|
In you are using SQL Server 2000 then this is not possible.
|
|
|
|
|
You can use SET @@rowcount or
create procedure ss(@s int)
as
EXEC 'select top ' + CAST(@s AS CHAR) + ' sno from summa1 order by newid()'
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
SomeGuyThatIsMe wrote: You can use SET @@rowcount or
I'm pretty sure you're thinking of SET ROWCOUNT and not SET @@ROWCOUNT. @@ROWCOUNT tells you how many rows were affected by a statement.
|
|
|
|
|
I suspect you are in sql server 2000, which will not accept a parameter for the top clause. In 2005 it would work. You need to create and execute the sql dynamically for it to work in 2000.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
in sql server 2000 try
create procedure ss(@s int)
as
SET ROWCOUNT @s
select sno from summa1 order by newid
SET ROWCOUNT 0
ROWCOUNT should turn pink..i havnt used it in a while so i may have typed it wrong. I do not believe that Rowcount is available in sql server 2k5, it was replaced with something but i dont remember what.
EDIT: ROWCOUNT is the correct one..@@ROWCOUNT wont work.
you could also use dynamic sql like
EXEC 'SELECT TOP ' + CAST(@s AS CHAR) + ' sno FROM summa1 ORDER BY newid'
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
modified on Friday, June 27, 2008 4:16 PM
|
|
|
|
|
I don't think this would work, I'm pretty sure @@rowcount is read only. I don't have sql server 2000 installed, but I have never seen @@rowcount being set, only ever read.
What I think is meant is set rowcount, but this cannot be set to a parameter, so you are back to dynamic sql
SomeGuyThatIsMe wrote: I do not believe that Rowcount is available in sql server 2k5,
Both rowcount (to set the number of rows returned) and @@rowcount (the number of rows affected) are both still alive and kicking in SQL Server 2005
Bob
Ashfield Consultants Ltd
|
|
|
|
|
It works fine i have it done in multiple places mostly in stored procedures, and in some other queries. SET ROWCOUNT it works well for randomizing the data you pull from a table with no PK. @@ROWCOUNT might be read only, ROWCOUNT is the correct one to use.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Sorry, you can set rowcount to a parameter, I just couldn't type when I tried it 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Although be aware that as far as I know, ROWCOUNT is being deprecated in 2K5 and it is removed from SQL Server 2K8 (or perhaps the one after 2K8)
|
|
|
|
|
yep thats what i read...they are replacing it with something, but i dont think it works quite the same.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
hi,
when i try to connect to a remote sql server 2008 from my application i get the below error.
'An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)'
but the remote sql server is accessible from other machines through the same application without any error.
thanks & regards
karthikeyan
|
|
|
|
|
Check this link[^]. Is there any other error number (apart from 40)?
|
|
|
|
|
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
this was the complete error message. please note that i am connecting to sql server 2008 on a server and sql 2005 which is installed with visual studio 2005 is in the local machine.
|
|
|
|
|
karthi84 wrote: please note that i am connecting to sql server 2008 on a server
Are you positive about this?
I am asking because it seems weird to me that the error message mentions SQL Server 2005 (When connecting to SQL Server 2005 ...) when you are connecting to SQL Server 2008.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
