Database Discussion Boards

Looking at the balance I'd say the sultana just worked out that he can vote, thanks!

Never underestimate the power of human stupidity
RAH

The question has "homework" written all over it. I notice he did eventually find a sucker willing to do it for him for free.

try this

select  mt.town , sum(mt.CompanySize) as TotalNrOfEmployees,
sum(mt.CompanySize)/(select count(*) from mytable) as AverageEmployeesPerTown,
sum(mt.CompanySize)/(select count(*) from mytable where companysize<20) as AverageEmployeesPerTownLessThen20
		
from mytable mt
group by mt.town
having (sum(mt.CompanySize) > 20)

order by ( sum(mt.CompanySize)) desc

This will give you result as

<br />
Town            TotalNrOfEmployees     AverageEmployeesPerTown    AverageEmployeesPerTownLessThen20<br />
Texas	        53	               8	                  13<br />
Maryland	49	               8	                  12

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.

www.cacttus.com

Thanks a million Blue_Boy.
It is good there is an exceptional person like you. Some have no clues, only to be throwing DIATRIBES.
Thanks a million Blue_Boy for sharing your knowledge.
Regards

modified on Monday, July 18, 2011 4:08 AM

Hope this helps
Input:

Declare @t table(CompanyID int identity, CompanyName varchar(20), Town varchar(20), Country varchar(20), CompanySize int)
insert into @t 
select 'CompanyA', 'Maryland', 'USA', 10 union all
select 'CompanyB' , 'Maryland', 'USA', 19 union all
select 'CompanyC', 'Maryland', 'USA', 20 union all
select 'CompanyD' , 'Texas', 'USA', 13 union all
select  'CompanyE', 'Texas', 'USA', 40 union all
select 'CompanyE', 'Florida', 'USA' ,4
Select * from @t

Query:

Select 
	t2.Town
	, t1.[Total Employees In the Town]
	, t2.[Avg No of employees(per town)] 
	, t2.[AVG Less than 20]
from

-- Query1 : The total number of employees in the town
	(
		Select 
			[Total Employees In the Town] =  SUM(a.CompanySize)
		from @t a 
		join (Select Town From @t Group by Town Having Count(Town) > 1 ) x 
		on a.Town = x.Town
	) t1
left join 
(
		Select 
			a.Town
			,a.[Avg No of employees(per town)]
			,b.[AVG Less than 20]
		from
		(
			-- Query 2: The average number of employees in a company (per town).
			Select	Town,[Avg No of employees(per town)] = SUM(CompanySize)/COUNT(Town) 
			From @t 
			Group by Town Having Count(Town) > 1 ) a

		join (

				-- Query 3 
				--			The average number of employees in a company (per town) only for companies which
				--			have less than 20 employees

				Select a.Town, [AVG Less than 20] =  SUM(CompanySize)/Count(a.Town) 
				from @t a 
				join (Select Town From @t Group by Town Having Count(Town) > 1 ) x on a.Town = x.Town
				where a.CompanySize < 20
				group by a.Town) b
				on a.Town = b.Town
) t2 on 1 = 1

Output:

Town	Total Employees In the Town	Avg No of employees(per town)	AVG Less than 20
Maryland	102	                      16	                    14
Texas	        102	                      26	                    13

Thanks

Niladri Biswas

Hi Niladri,
Your answer and that of Blue_boy have really expanded my thoughts.First I am very grateful. Meanwhile, the results from the two queries are different. The problem lies in the filtering.
Sincerely, I am really grateful.I will work further on your posted queries.
Regards,
Current

Hello CP,

I'm messing around with SSIS and have run into a problem when trying to get the DataReader Source to work.
The problem is simple;
I'm trying to set the SqlCommand property to point to a stored procedure:
EXECUTE [dbo].[spMyProc] '20110101', '20110131'

The problem does not lie in calling the SP but rather with something in the SP.
My SP makes use of Temp table and it seems it cannot find it:

Error at Data Flow Taks[DataReader Source[1]]: System.Data.SqlClient.SqlException: Invalid object name '#tmpCalcTable'.
 at System.Data.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

The Stored Procedure creates a temporary table and later deletes it, but it seems it's either not doing that or can't access the it.
I've tried making the temp table global by using a double hash (##) instead of one but without luck.

Does anyone have an idea how I can fix this?

Have you tried using a table variable instead of a temp table?

This article has some useful information: Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance[^].

"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

The error you're getting back from SQL suggests that there's a temp table being referenced that is either not created, is dropped before it is called, or is otherwise orphaned somehow in the code. Can you run the SP through code (in SSMS) with the appropriate input parameters and it will execute correctly? If so, then it's something in your .NET code or some other setting/parameter within the SSIS package. If not, can you post your TSQL code? Thanks

modified on Friday, June 24, 2011 8:22 AM

It works fine in the Server manager.
I've got a workaround though, create the temp table once (outside of the query) and truncate it at the start of the query. This also means I'm no longer dropping it.
It's a strange little bug though.

The first rule of CListCtrl is you do not talk about CListCtrl - kornman

Ok, cool. Glad to assist if you should need further assistance. Cool | :cool:

This may be a stupid question but is there a way that I can a statement in mySQL.
I need to change how the SHOW statement works.
I realize I could do this with a different function of my own but I am needing to use the existing function if possible.
Any help is appreciated.

Humble Programmer

What excately is your question?

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.

www.cacttus.com

Can I change what the SHOW statement does....
For example it runs a certain SQL statement that is defined somewhere in mySQL.
I wonder can I change that statement to do something different or maybe make it call a different statement.

Humble Programmer

It's an internal command - you can't modify the behaviour of the internal commands otherwise you could end up breaking other functionality that relied on that behaviour.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Could I maybe overload it with another parm?

Humble Programmer

You tell us. Look at the docs for it. They tell you what it does. If it isn't there then it doesn't do it.

http://dev.mysql.com/doc/refman/5.0/en/show.html[^]

Do you have the source-code of the function?

There's your answer Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Hello CP,

I've set up a job to perform a stored procedure at a given time, nothing special.
In the step (which triggers the SP) I've specified the output file in the 'advanced' section.
The output in the file is the execution time (moment) and the 'select' form the SP.
My problem is the output file format.

Does anyone know how to set it to CSV or perhaps another solution?

Is the goal to get the data output from the stored procedure into a CSV file? That is not possible (in a useable format anyway) in the job step's advanced properties. Specifying a file path and file name there allows SQL to save the detailed information regarding the job's execution to a text file. To get your stored proc output data into a CSV file, a different coding approach is needed (like SSIS, BCP, or the like). Or, did you actually want to get the detailed information about the job running into a CSV file?

Apologies if I've misunderstood your question. Cool | :cool:

modified on Thursday, June 23, 2011 5:41 PM

You understood it perfectly.
I had been looking at BCP and tried to format it correctly (from the command prompt, not sqlcmd), but it bugged on my nested stored procedures.
Haven't looked at SSIS yet, gonna do that now.
But I'm getting off-topic.

You answered my question.

Hi all,

Is there any difference between the following two sets of queries?

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
 UNION 
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS as STATUS, '' , '' 
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
						join RMASTER d on a.ID_NUM = a.ID_NUM
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

and

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and (a.RESIDSTS = 'R' or a.RESIDSTS <> 'R') and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

well in one there is "UNION" used but will there any difference in the output or are these both queries same as per the output is concered?

suchita

Yes, those should give the same result. They might be a bit different sorted I think, but in the end the same data should be in the output.

Edit: I oversaw one small thing. The second query in the union doesn't return the values of d.ROOM_DESC, d.ROOM_PHONE, but rather two empty strings in those columns. In your combined query these columns will be filled.

ya thank you..

suchita

Hi all,

I have two sets of query which if run alone, runs without any errors but when i try to use UNION between these two queries, it gave me error like this:

Error converting data type varchar to numeric.

The sql queries are

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
 UNION 
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS as STATUS, '' , '' 
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
						join RMASTER d on a.ID_NUM = a.ID_NUM
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS &lt;&gt; 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

but when i run

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

or

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS as STATUS, '' , '' 
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
						join RMASTER d on a.ID_NUM = a.ID_NUM
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

it runs.

Can anyone help me solve this out?

suchita