Database

How do you use the AVG api in sql to manage grouped output?

10-Aug-12 23:52

Member 4006546 wrote:
How to convert Physical XML files into Sqlserver Tables?

..documentation[^].

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Xarzu10-Aug-12 13:04

Xarzu

10-Aug-12 13:04

How do you use the AVG api in sql to manage grouped output? For example:
Given a SQL Table “Employee”

Name Salary City

John Doe 15000 Seattle
Jane Doe 30000 Redmond
Tim Wayne 25000 Seattle

Write a SQL query that would return the average salary per city in the format shown below:

City Salary

Seattle 20000
Redmond 30000

Re: How do you use the AVG api in sql to manage grouped output?

Paul Conrad10-Aug-12 20:18

Paul Conrad

10-Aug-12 20:18

Xarzu wrote:
Write a SQL query that would return the average salary per city in the format shown below:

For some reason this sounds like homework. What have you done so far for a query?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: How do you use the AVG api in sql to manage grouped output?

Xarzu11-Aug-12 13:30

Xarzu

11-Aug-12 13:30

Select City, AVG(Salary) from Employee where ... I don't know the rest

Where city == city or something like that?

Re: How do you use the AVG api in sql to manage grouped output?

Paul Conrad11-Aug-12 13:32

Paul Conrad

11-Aug-12 13:32

You could try:

SQL

select city, avg(salary) from Employee group by city

This will give you the average grouped by cities. How to sort the cities in the order you want to order by in your expected result, I'm leaving that as an exercise Big Grin | :-D

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

modified 11-Aug-12 20:19pm.

Re: How do you use the AVG api in sql to manage grouped output?

Eddy Vluggen10-Aug-12 22:17

Re: How do you use the AVG api in sql to manage grouped output?

10-Aug-12 22:17

Xarzu wrote:
How do you use the AVG ~~api~~ function in sql

It's not an "API".

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Niladri_Biswas21-Aug-12 22:51

Niladri_Biswas

21-Aug-12 22:51

All Version of Sql server

SQL

Select City, AVG(Salary) AS Salary
FROM @t
Group By City
Order By City DESC

This can also be done by treating AVG function as Analytical function which is available since SQL Server 2005

SQL

Select City,Salary From
(
    Select
        Rn = Row_Number() Over(PARTITION BY City Order By (Select 1))
        ,City
        ,AVG(Salary) OVER(PARTITION BY City) AS Salary
    From @t) X
Where X.Rn = 1
Order By City DESC

The answer for both the cases

City Salary
Seattle 20000
Redmond 30000

Niladri Biswas
(Code Project MVP 2012)

How to store a standard selection?

Jörgen Andersson10-Aug-12 9:57

Jörgen Andersson

10-Aug-12 9:57

I have a question that's more of the hypothetical kind.

I have two tables:

Users
userid	username
1	John Doe
2	Jane Doe

email
userid	email
1	john.doe@myworkmail.com
1	john.doe@myprivatemail.net
1	john.doe@someothermail.org
2	jane.doe@myworkmail.com
2	jane.doe@myprivatemail.net
2	jane.doe@someothermail.org

Now I'd like to make one (or zero) email address per user the default address.

On an ANSI compliant system I could simply add a nullable column to the email table and add a check so that the value can only be 'DEFAULT' or null and add a unique composite key on this column and the userid column.
This would of course not work on certain databases that don't allow more than one null value when having a unique key.

So what other solutions are there? Preferrably not allowing null values.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

R. Giskard Reventlov10-Aug-12 10:02

R. Giskard Reventlov

10-Aug-12 10:02

How about a bit field (0/1;true/false)? Set the default to 0, not null.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Jörgen Andersson10-Aug-12 10:09

Jörgen Andersson

10-Aug-12 10:09

How would that allow only one emailaddress to be set to default?

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

R. Giskard Reventlov10-Aug-12 10:41

R. Giskard Reventlov

10-Aug-12 10:41

The default is set to true, the rest to false.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Eddy Vluggen10-Aug-12 10:11

10-Aug-12 10:11

An extra table to store only a reference to the user, and a reference to his default email;

userId	emailId
1	1
2	4

..which would work best if email would get an identity-like field.

--edit;
Add a UNIQUE constraint on the userId-column Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Database status check and report

Jörgen Andersson10-Aug-12 10:31

Jörgen Andersson

10-Aug-12 10:31

Where's the 'slapping my forehead' icon when I need it? I guess D'Oh! | :doh:

will have to do.
Thumbs Up | :thumbsup:

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

leketekoa10-Aug-12 4:12

leketekoa

10-Aug-12 4:12

Hi all,I want to write an application(web or desktop) which will monitor different database status(status:conncetion error,running or down e.t.c) and report back to say:database1 is not down, oracle database2 is down or connection error etc. So I dont really have an idea where to start.Coding using C#.thanx.

Re: Database status check and report

David Mujica10-Aug-12 5:10

David Mujica

10-Aug-12 5:10

You could begin by something as simple as:

1) Make a connection to the database New SqlConnection(GetConnString())
2) Open the connection SQLconn.Open()
3) Issue some sort of simple command SQLcmd = New SqlCommand("select count(*) from customers", SQLconn)
4) Get data from the DB r = SQLcmd.ExecuteScalar

5) Wrap all of this code in a Try-Catch block and if successful, then you can safely assume that your connection to the database is up and the database is running.

Good luck Thumbs Up | :thumbsup:

Re: Database status check and report

leketekoa13-Aug-12 4:30

leketekoa

13-Aug-12 4:30

Thanx a lot.I even add more functionalities/Methods Big Grin | :-D

Re: Database status check and report

jschell10-Aug-12 12:46

jschell

10-Aug-12 12:46

leketekoa wrote:
oracle database2 is

Did you start by looking for existing solutions?

As an example you might want to start with the following in google: nagios oracle

leketekoa wrote:
nd report back

What that means can add significantly to scope of the project.

Symantec Q&A File Conversion

snowman539-Aug-12 12:54

snowman53

9-Aug-12 12:54

I have a client that was using Q&A until the old computer died yesterday. He has backups of the data base, but not the program itself and Symantec has not supported Q&A for a long time.

Can anyone point me to a low cost (or free) conversion utility to read these files?

Or a copy of Q&A that I can use long enough to convert them to fixed ascii?

I did find Sesame, which seems to have taken over Q&A, but they want $350 for the program - which is a lot to pay for a few file conversions.

I also found conversion services, but again a lot of money.

Any help appreciated!

Eddy Vluggen9-Aug-12 22:21

9-Aug-12 22:21

snowman53 wrote:
I did find Sesame, which seems to have taken over Q&A, but they want $350 for the program

Also found an online-converting service, but they'll be asking money too. If the data is worth more than those $350, go for it - and explain the concept of backups and/or a vendor lock-in.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

snowman5310-Aug-12 11:51

snowman53

10-Aug-12 11:51

They haven't updated their software since the mid 90's and are upset I can't re-install Q&A without the original copies!

Eddy Vluggen10-Aug-12 22:13

10-Aug-12 22:13

snowman53 wrote:
are upset I can't re-install Q&A without the original copies!

Not very rational, is it? It'd be hard for anyone to install a copy without an installer Smile | :)

Without that copy there's not much you can do, except trying one of the expensive converters. Perhaps your client could take the time and look for a second-hand version?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Mycroft Holmes12-Aug-12 18:18

Mycroft Holmes

12-Aug-12 18:18

I know nothing about Q&A but a lot of the 90s databases were based around DBase formats and there was a whole family of drivers for reading various dbase files. The most comprehensive was DBase IV and the foxpro drivers.

Never underestimate the power of human stupidity
RAH