Summing Amount by Group field - Database Discussion Boards

Jörgen Andersson30-Mar-09 23:37

30-Mar-09 23:37

Mycroft Holmes wrote:
STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like:
Recipie
Ingredients

I think that is what he wants to do.

"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon

Re: Reading Columns

anup keshari30-Mar-09 21:47

anup keshari

30-Mar-09 21:47

select column1,convert(varchar(20),isnull(column2,''))+''+convert(varchar(20),isnull(column3'')) as coluumn2 from tablename

use the above query and write like above for all the ingredent column .

lets me know if any things is needed.

Re: Reading Columns

anup keshari30-Mar-09 22:02

anup keshari

30-Mar-09 22:02

sorry for the above reply

use this

select coumun1,column2 from table1
where column2 is not null
union all

select coumun1,column3 from table1
where column3 is not null
union all

select coumun1,column4 from table1
where column4 is not null

here for three column u have to write for all column

Re: Reading Columns

anup keshari30-Mar-09 22:33

anup keshari

30-Mar-09 22:33

for sqlserver 2005

use unpivot method

Re: Reading Columns

ffowler31-Mar-09 2:05

ffowler

31-Mar-09 2:05

Whoa, Whoa guys. I may not have explained myself clearly from the responses I have received.
The database tables are in the correct format as you have suggested. I need a query to pull the data in the format I mentioned to another party. I am not certain how they will be utilizing the data in this manner but that what has been spec'd out to me.

Thank you Anup for your assistance and I will further explore the command you have suggested.

Looking for a Web/SQL reporting subsystem

Max Stayner30-Mar-09 9:47

Max Stayner

30-Mar-09 9:47

Hi,

I'm looking for a SQL-based reporting system with the following features for running against a SQL Server 2005 database.

- SQL for each report is to be hand coded (i.e. NOT 'ad-hoc/drag-n-drop end user reporting')
- Users will select parameters at run time
- Output to browser (don't care about controlling hard-copy formatting)
- Must be able to include complete T-SQL.
- Ideally include support for transactional operations

I wrote something very much like this in my last job, but would rather buy this time.

Anyone used or even know of such a beast? I've scoured Google with no result. Everything I've found is either 'Build reports without knowing SQL' or 'use this toolkit to integrate a specific report into your application.

Thanks,

Max.

Re: Looking for a Web/SQL reporting subsystem

Mycroft Holmes30-Mar-09 21:45

Mycroft Holmes

30-Mar-09 21:45

It's called SQL Server Reporting Services. DevExpress also do an excellent reporting tool.

Never underestimate the power of human stupidity
RAH

Summing Amount by Group field

BijayaSharma30-Mar-09 9:36

BijayaSharma

30-Mar-09 9:36

Hi There

I need to create report that needs to group by "[B]status[/B]= Funded and Not funded" , "[B]Institute[/B]" body is title

I need to find the [B]Total amount [/B] that Institute had earned for different Research Proposals. In One Proposal there could be Group of people involved

I am creating Report using this Query

SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.Amount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
WHERE (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
My result is some thing like this

Title Inst Type Person Role SID Amount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000

B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000

c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000

I want the total By Inst = Del = 14,00,000
and Inst= comp = 1,200,000

I have created the group by Institute, added text box at the footer of Institute and assigned = Sum([Amount]) to the control source of that text box. I am not sure Why I am not getting the result

Thank you
Bijaya

Re: Summing Amount by Group field

Anubhava Dimri31-Mar-09 1:09

Anubhava Dimri

31-Mar-09 1:09

You are not Using Group By method.For your knowledge U should Use Group By Query.

Like : SELECT A.CustomerName, A.SUM(OrderPrice) FROM Sales as A GROUP BY A.CustomerName

Best Regard
Anubhava Dimri

mailto: anubhava.prodata@gmail.com

Re: Summing Amount by Group field

BijayaSharma31-Mar-09 4:19

BijayaSharma

31-Mar-09 4:19

Thank you Anubhava,

This is my Group By Query :

SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Sum(Proposal.Amount) AS SumOfAmount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
GROUP BY Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.DateSubmitted
HAVING (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));

Still result remains the same

Title Inst Type Person Role SID SumOfAmount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000

B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000

c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000

At Institute footer cntains Text feild =sum(SumOfAmount) for Del= ($500,000 *3) + ($900,000 * 3) = $ 4200,000
for comp= $2400,000
Where As I want this
By Inst = Del = 14,00,000
and Inst= comp = 1,200,000

Thank you
Bijaya

Re: Summing Amount by Group field

BijayaSharma1-Apr-09 6:28

BijayaSharma

1-Apr-09 6:28

Hi Everybody,

I am working on the proble With the first query. I am not sure why I am not getting the Sum Amount by Institute(Inst)
Just to get Total Amount by Institute. I created another Query that is:

SELECT Institutions.IName,
Sum(Proposal.Amount) AS SumOfAmount,
Proposal.sid
FROM Proposal INNER JOIN (Institutions INNER JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID

GROUP BY Institutions.IName, Proposal.sid
HAVING (((Proposal.sid)=1 Or (Proposal.sid)=2));

Datasetview of the above Query is:

Inst SumAmount SID(status)
Del 14,00,000 funded
comp 12,00,000 not-funded

But when I assign this to Combo box at the Footer of Institute Group. I am not getting the accepcted result. I am getting this one.

Del = 500,000
Comp = 1200,000

Could onyone help me why this is happening? Confused | :confused:

I am counting on you, my project is due comming Monday. help Needed!

thank you
Bijaya Cry | :((

How do i display Values Horizonatally In Crystal Reports

Veerkumar Patil30-Mar-09 4:31

Veerkumar Patil

30-Mar-09 4:31

Re: How do i display Values Horizonatally In Crystal Reports

anup keshari30-Mar-09 4:58

anup keshari

30-Mar-09 4:58

you should used cross tab report type . it will available when u will add the report in own solution explorer.

lets me know if any things is needed.

About Database Certifcation

Isaac Gordon30-Mar-09 2:03

Isaac Gordon

30-Mar-09 2:03

Hi Experts
I facing a great Problem Pls Help.
I Have Database in SQL Server 2005.i giving it to My Client end.
i Want To Protect My Database Not To Open or Modified By Another User.
How To Make Certificate of Database and Use It at Client End So Another Person Not To Open My Database

Re: About Database Certifcation

Eddy Vluggen30-Mar-09 3:14

Eddy Vluggen

30-Mar-09 3:14

I don't think that you can sign a SQL-database the way you sign a Word-document. A database is meant to be changed, if you want to restrict access then dive into permissions, users, passwords, policies and logons Smile | :)

I are troll Smile | :)

Deploying SQL Server

kbalias30-Mar-09 1:16

kbalias

30-Mar-09 1:16

Hi
I am using Visual Studio 2005 and C# to develop a Windows application in the cancer department where I work. It is a small patient management system. I was asked to use MS Access as the backend. For obvious reasons I would prefer to use SQL Server. Unfortunately the network guys running the hospital network are very sticky with what is allowed on their servers. This is quite understandable, but it does make things a bit difficult. We are only allocated a folder on the network that our department has access to. With MS Access it is easy enough. We just copy the MS Access database into the folder and point the application to it.

If I can use SQL Server how would I deploy it? Must SQL Server be installed on the server first? I will probably use SQL Server 2005 Express.

Thanks.

Kobus

Re: Deploying SQL Server

Vimalsoft(Pty) Ltd30-Mar-09 4:00

Vimalsoft(Pty) Ltd

30-Mar-09 4:00

SQL server must be installed on the Server and you continue as usuall and point to SQl that Access and that means your Code will change in the DAL , you will be using sqlclient not oledb

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

Re: Deploying SQL Server

Mycroft Holmes30-Mar-09 21:56

Mycroft Holmes

30-Mar-09 21:56

Your'e network people are idiots (or have strict policies about what they can allow departments) Smile | :)

. You need some arguments that will move them off this policy position or get an exception. They will have some database they use and may allow you to use part of it, may be oracle or one of the other enterprise DBs.

Our network team for instance will not allow Access anywhere near a server, it has been known to crash a server and they are terrified of it. Unless properly managed it can grow horrendiously. Are you sure you do not need to install the jet drivers or even office on the server? This would be a good argument against Access.

Good luck.

Never underestimate the power of human stupidity
RAH

How to run exe file using xp_cmdshell

Krushna Sahu30-Mar-09 0:08

Krushna Sahu

30-Mar-09 0:08

Hello everyone,

I've a exe file in C:\. I need to run it from stored proc using xp_cmdshell. Please note that I'm using SQL Server 2000.

My stored proc is :

Create proc callExe
As
EXEC master.dbo.xp_cmdshell 'C:\PrintToPDFConsole.exe'

After executing this, the output is :

NULL
Unhandled Exception: System.ComponentModel.Win32Exception: No application is associated with the specified file for this operation
at PrintToPDF.Program.Main(String[] args)
NULL

and the exe file did not run.

Please help in this.

Also, I've tried with common exe file like 'notepad.exe' like:

ALTER proc callExe
As
EXEC master.dbo.xp_cmdshell 'notepad.exe'

When I executed this, the execution is going on and not showing any output. The execution time is more that 10 mins and going on...
Then I forcefully closed the isqlw.exe from Task Manager.

Is that mean exe file can't be executed in sql server 2000 using xp_cmdshell ?

--Krushna Chandra Sahu
prfkrushna@gmail.com

Re: How to run exe file using xp_cmdshell

knagnayak30-Mar-09 0:15

knagnayak

30-Mar-09 0:15

You cannot run exe which has GUI. You can run exe which is running in background process.

Re: How to run exe file using xp_cmdshell

Krushna Sahu30-Mar-09 2:55

Krushna Sahu

30-Mar-09 2:55

Yes, I forgot to write there that the exe file does not have a GUI.
It will automatically convert the any file to pdf file and save to My Document folder.

--Krushna Chandra Sahu
prfkrushna@gmail.com

SQL Server Memory Usage Problem

VikashGohil29-Mar-09 22:16

VikashGohil

29-Mar-09 22:16

Hello,

My Question is related to SQL Server Memory Usage,

I have a VB.net application with MSDE.

When i run my application, the sqlserver.exe in Task Manager uses memory of about 20 MB,

Then when I View any Crystal Report created report, the memory goes on increasing as the report is loaded and if reaches about 150 to 200 MB, but even after closing the report and also closing the application, SQL Server does not release memory.

So when we again start the application, again memory goes on accumulating but is never released.

Memory is released only when we Stop and start the SQL Server or restart the PC.

Can anyone tell me how to make SQL Server Release Memory from my VB.net application.

Thanks in Advance.

Re: SQL Server Memory Usage Problem

Lambin6-Apr-09 18:24

Lambin

6-Apr-09 18:24

Hello,

sqlservr.exe gives back never the memory except when there is a memory pressure, that's to say when the operating system is needing memory for itself or for another application. For example, SQL Server is releasing memory when too much paging is occuring.
When the operating system is short of free memory, sqlserv.exe frees RAM memory ( K. Delaney neede about 20 pages in her book (Inside Microsoft SQL Server 2005 : the storage engine ) to explain the
management of the memory by SQL Server.
You may limit the amount of memory used by a sqlservr.exe ( there is 1 sqlservr.exe for each started instance of SQL Server ) thru the options Min server memory and Max server memory ( right-click on an instance , properties, tab memory ). For my 3 instances, i have limited Max server memory to 500 MB, so i am sure that never a sqlservr.exe will use more than 500 MB ( in fact, it is rare that the 3 instances are running, so i am sure that, with 2 GB of RAM , i am sure that the OS and applications other than sqlservr will have at least 500 MB of free memory.
You can't manage and free memory from your VB.net application.
But a connection to a server or database is one of the heaviest operation and i prefer to limit the number of connections by opening the connection once ( at the beginning of the application ) and closing the connection when exiting the application ( don't forget to close the connection in the exceptions treatement )

Service Account - Account Name

sawerr29-Mar-09 6:57

sawerr

29-Mar-09 6:57

Hi
While installing SQL Server 2008, it asks for Service account and account name, pls click: [^]

Here, what are the meanings of options: "NT AUTHORITY\SYSTEM", "NT AUTHORITY\NETWORK SERVICE" etc..?
Are these registry keys or something like that. Where can i find information about this stuff?
I don't even know which subject they are related and where i must start to search about these.

Ihanks...

sql noob question on COUNT

Eytukan29-Mar-09 4:12

Eytukan

29-Mar-09 4:12

A simple table:

create table temptable(userid int,username varchar(100),score int)

Values inserted:

insert into temptable values(1,'BOB',17)
insert into temptable values(1,'BOB',21)

Test:

select * from temptable where userid =1

Result:

1,BOB,17
1,BOB,21
Returns 2 rows. Fine.

The requirement: Find number of times BOB has scored above 20.
Expected result

1,BOB,1 //Where the last "1" says that he's got 1 score above 20 (the count)

My attempt:

select userid,username,count(*) from temptable where userid=1 group by userid,username

Here how do I specify a condition for the "Count()"? It just need to count score>20 for BOB.

I'm a rock bottom sql noob. Any help?

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.