|
In a single select this will do it:
SELECT AGE_CATEGORY, SUM(MALES) AS MALES, SUM(FEMALES) AS FEMALES,
convert(float,(sum(males) + sum(females))) * 100 / (SELECT SUM(MALES) + SUM(FEMALES) FROM AGE_CATS) as PCent
FROM AGE_CATS GROUP BY AGE_CATEGORY
However it would be better to do
DECLARE @tot float
select @tot = SUM(MALES) + SUM(FEMALES) FROM AGE_CATS
SELECT AGE_CATEGORY, SUM(MALES) AS MALES, SUM(FEMALES) AS FEMALES ,
convert(float,(sum(males) + sum(females))) * 100 / @tot as Pcent
FROM AGE_CATS GROUP BY AGE_CATEGORY
hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks mate
I owe you
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
No problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi i have a table that store my subject.
each subjct has a parentid that refer to one subject id in itself table.
how can i write query that return subject with number of there childs?
sepel
|
|
|
|
|
Where are stored childs of subject? In which table?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
can you give structure of tables you are talking about and structure of output you need?
|
|
|
|
|
You might want to have a google and look at recursive common table expressions (CTE) (if you're on SQL2005).
|
|
|
|
|
OK, here is the deal. I've got a table with 2 columns: "ID"(int), "Name"(string). "ID" - is a unique key field used in relations with other tables, but the values of the "Name" should be unique as well. I need to add more data into this table, but not to create some duplicate information.
I know about INSERT DISTINCT, but I am not using an SQL command, instead I use a VS generated TableAdapter (in my case it is named "companiesTableAdapter"). Here is a small code snippet:
this.companiesTableAdapter.Insert("XYZ Company");
this code does insert the string into a table, but it doesn't care if "XYZ Company" is already there.
(the DB itself is created in Access2003, if this may be helpful)
If someone has done something similar, I would really appreciate their help.
|
|
|
|
|
First check if that name exists, in case if exists then don't insert otherwise do insert.
example:
if(select count(name) from tablename where name='value')=0 <br />
begin<br />
insert into tablename values ('idvalue','namevalue')<br />
end<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
Worked great! Thanks a lot
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You can add an unique index on the name column.
|
|
|
|
|
How can I do that? (Add a unique numerical index to a column)
|
|
|
|
|
Simply really
Create unique index fred on myTable(myColumn)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
I need to be able to allow characters like (') into a column in MYSQL, I have tried using 'text' and 'blob' but both of these seem to reject any inserts. the only way I found is to place a backslash infront of the character (\').
The only problem Is the application will be public and I will not be able to explain this to every user.
oh, also using fckeditor to forat text.
Any ideas?
Really appreciate your thoughts.
D.
|
|
|
|
|
example:
<br />
'this string contain''singlequotes'''
result
<br />
this string contain'singlequotes'
-----------
<br />
'this string contain''singlequote'
result
<br />
this string contain'singlequote
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
Is that valid for MYSQL? I thought that was SQL Server syntax?
|
|
|
|
|
Yes it is SQL Server syntax.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
dgreat1 wrote: Any ideas?
Use parameters, let ADO.NET figure it out.
|
|
|
|
|
Good Afternoon,
I am fairly new to access coding. I am trying to write a SQL in access that will get make a packing slip based upon the return shipping address, but if the return shipping address is null, then the shipping would be the order address.
I started with this, but it is not working
=IIf(IsNull([Returns]![Address]),[ Orders]![Addr1], [Returns]![Address])
& IIf(IsNull([Returns]![Address]),[ Orders]![Addr2],[Returns]![Address])
& IIf(IsNull([Returns]![Address]),[ Orders]![Addr3], [Returns]![Address])
& IIf(IsNull([Returns]![City]),[ Orders]![City],[Returns]![City])
& IIf(IsNull([Returns]![State]),[ Orders]![State],[Returns]![State])
& IIf(IsNull([Returns]![Zip]),[ Orders]![Zip],[Returns]![Zip])
& IIf(IsNull([Returns]![Country]),[ Orders]![Country],[Returns]![Country])
& IIf(IsNull([Returns]![Phone]),[ Orders]![Phone],[Returns]![Phone])
So, what I wanted was the person wanted to return a product, and his return address is different from his order address, So I wanted some kind of jet SQL script to say if this person put in a return address then the packaging slip would use that but if that person did not put in a return address, then it would use the order address where that return address is null.
I appreciate anyones help on this, thank you
|
|
|
|
|
IIRC you can create functions within Access where you can write the code to return 1 address (decision is made in the function) and then use the function in your select statement.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can I get an example of this?
|
|
|
|
|
Nope, I have not used Access in 10 years and that is the way I like to keep it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can anyone tell me how to change time for a DB in SQL Server 2005? I need to correct the time for a database, thanks.
|
|
|
|
|
You set the time of the windows server.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
