Get 2 Decimal Places in Avg

I am running the query below but the results show only the integer portion.
I know the results should be values like "2.33" instead of "2". How do I get
the correct result format?
Wayne
========================================
SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
Count(judgeevalsbyunits.UnitID) As "Count of Evals",
Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
FROM judgeevalsbyunits
INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaptionIf the source columns are only integer values, you only get those back, if
you want to get a decimal value back, you have to convert the source columns
to the appropiate format, eg. AVG(CONVERT(decimal(8,2),YourColumn))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> schrieb im Newsbeitrag
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Try,
...
Avg(judgeevalsbyunits.RegScoring * 1.0) As "Reg Scoring Avg",
...
The average value of an integer column is an integer value. Cast the value
to decimal to get a decimal avg.
Example:
select
avg(c1 * 1.0)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
select
avg(c1)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
go
AMB
"Wayne Wengert" wrote:

> I am running the query below but the results show only the integer portion
.
> I know the results should be values like "2.33" instead of "2". How do I g
et
> the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>
>|||convert the fields into the decimal format like
AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
cheers
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Thanks guys. That is exactly what I needed.
Wayne
"keber" <k@.c.com> wrote in message
news:O6fKoirZFHA.3152@.TK2MSFTNGP14.phx.gbl...
> convert the fields into the decimal format like
> AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
> cheers
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>

Generating a daily statistic report SQL

Hi,

I Have a table below.

Query

PKEY id int

name varchar(128)

date_add DateTime

What is the SQL statement to get the number of query on each day?

the output should be date and quantity. There should still be an output even if there is no query on that day.

The only way I can think of is by a table-value UDF. (rough design)

function(startdate, enddate)

{

for each day from start to end

insert into result select count(*) from Query where date_add = currentDate

return

}

Is there a more efficient way to do this?

Thanks,

Max

Something like this will do.

Code Snippet

select dateadd(day,datediff(day,0,date_add),0) [day], count(*) [quantity]

from your_table_goes_here

where date_add between @.startdate and @.enddate

group by dateadd(day,datediff(day,0,date_add),0)

If you need to return "0" for unaccount date, you will need a calendar (or number) table to seed the date to be calculated by.

Generate XML document problem

I'm having the same problem as described below. Although this was
posted on usenet a few years ago, a solution was not posted, and I
can't find one on the internet anywhere. Does anyone have any idea as
to how I might workaround this? Note that I am not sorting on any of
the varchar fields that are causing the problem.
Thanks,
Aaron

> Here is the problem,
> I need to generate an XML document using FOR XML Explicit. My select
> works fine but I run into a problem when I try to do an order by with
> a row that is greater than 8094 bytes. SQL Server outputs the error
> "Cannot sort a row of size 96555, which is greater than the allowable
> maximum of 8094".
> The order by is very important in my query, because without it. I will
> not get the XML document in the correct schema.
> I have researched this issue and have come to the conclusion there is
> not a way I know of to get around this limitation. I tried using the
> query hint "Robust Plan" and "Force Order" and that did not work.
> A solution that I thought of, but don't know if it will work and how
> to do in SQL server is to first generate the XML schema without the
> TEXT column fields and then do another query to load the text columns
> into the XML document.
> Can I and how do I do all of this in a Stored Procedure?
> Or is there a better workaround for my problem?
> Thanks in advance
>
> --
> B. HarilalHi Aaron
Can you please provide some more information? Is it the order by clause that
may contain too much data? Or the rows that you sort?
It would be very useful if you can provide a simple repro.
Thanks
Michael
"AaronS" <gcsdba1@.yahoo.com> wrote in message
news:1140618933.285133.96480@.z14g2000cwz.googlegroups.com...
> I'm having the same problem as described below. Although this was
> posted on usenet a few years ago, a solution was not posted, and I
> can't find one on the internet anywhere. Does anyone have any idea as
> to how I might workaround this? Note that I am not sorting on any of
> the varchar fields that are causing the problem.
> Thanks,
> Aaron
>
>|||Here's an example of what the query is trying to do. I know this query
is not logically correct, but hopefully it gives you an idea of what
the actual query is doing.
SELECT
1 as Tag,
NULL as Parent,
reportID as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM report
UNION ALL
SELECT
15 as Tag,
5 as Parent,
NULL as [reportData!1!id],
reportID as [comments!5!reportCommentID!hide],
details as [comment!15!details] -- varchar(8000)
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM comment
UNION ALL
SELECT
30 as Tag,
10 as Parent,
NULL as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
reportID as [Notes!10!reportCaseID!hide],
NoteDesc as [Note!30!noteDesc] -- varchar(8000)
FROM note
-- Note that the sort is not on any of the "long" varchar fields
ORDER BY
[reportData!1!id],
[comments!5!reportCommentID!hide]
[Notes!10!reportCaseID!hide]|||Hi Michael,
I realize my SQL example is very crude (especially since I left out any
join conditions), but does it get the idea across about where the
problem lies? It appears to be the rows I am attempting sort that
contain too much data for SQL Server to handle. The columns specified
in the order by clause do not appear to be the culprit as this error
occurs only when the selected data itself exceeds the SQL Server limit
of 8094 characters. Can you (or anyone else reading this) provide any
insight? I would really appreciate it. I'm at a loss of where to go
from here except to start truncating the lengthy varchar strings. I
don't even know if I have the option to do that.
Aaron

Generate XML document problem

I'm having the same problem as described below. Although this was
posted on usenet a few years ago, a solution was not posted, and I
can't find one on the internet anywhere. Does anyone have any idea as
to how I might workaround this? Note that I am not sorting on any of
the varchar fields that are causing the problem.
Thanks,
Aaron

> Here is the problem,
> I need to generate an XML document using FOR XML Explicit. My select
> works fine but I run into a problem when I try to do an order by with
> a row that is greater than 8094 bytes. SQL Server outputs the error
> "Cannot sort a row of size 96555, which is greater than the allowable
> maximum of 8094".
> The order by is very important in my query, because without it. I will
> not get the XML document in the correct schema.
> I have researched this issue and have come to the conclusion there is
> not a way I know of to get around this limitation. I tried using the
> query hint "Robust Plan" and "Force Order" and that did not work.
> A solution that I thought of, but don't know if it will work and how
> to do in SQL server is to first generate the XML schema without the
> TEXT column fields and then do another query to load the text columns
> into the XML document.
> Can I and how do I do all of this in a Stored Procedure?
> Or is there a better workaround for my problem?
> Thanks in advance
>
> --
> B. Harilal
Hi Aaron
Can you please provide some more information? Is it the order by clause that
may contain too much data? Or the rows that you sort?
It would be very useful if you can provide a simple repro.
Thanks
Michael
"AaronS" <gcsdba1@.yahoo.com> wrote in message
news:1140618933.285133.96480@.z14g2000cwz.googlegro ups.com...
> I'm having the same problem as described below. Although this was
> posted on usenet a few years ago, a solution was not posted, and I
> can't find one on the internet anywhere. Does anyone have any idea as
> to how I might workaround this? Note that I am not sorting on any of
> the varchar fields that are causing the problem.
> Thanks,
> Aaron
>
|||Here's an example of what the query is trying to do. I know this query
is not logically correct, but hopefully it gives you an idea of what
the actual query is doing.
SELECT
1 as Tag,
NULL as Parent,
reportID as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM report
UNION ALL
SELECT
15 as Tag,
5 as Parent,
NULL as [reportData!1!id],
reportID as [comments!5!reportCommentID!hide],
details as [comment!15!details] -- varchar(8000)
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM comment
UNION ALL
SELECT
30 as Tag,
10 as Parent,
NULL as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
reportID as [Notes!10!reportCaseID!hide],
NoteDesc as [Note!30!noteDesc] -- varchar(8000)
FROM note
-- Note that the sort is not on any of the "long" varchar fields
ORDER BY
[reportData!1!id],
[comments!5!reportCommentID!hide]
[Notes!10!reportCaseID!hide]
|||Hi Michael,
I realize my SQL example is very crude (especially since I left out any
join conditions), but does it get the idea across about where the
problem lies? It appears to be the rows I am attempting sort that
contain too much data for SQL Server to handle. The columns specified
in the order by clause do not appear to be the culprit as this error
occurs only when the selected data itself exceeds the SQL Server limit
of 8094 characters. Can you (or anyone else reading this) provide any
insight? I would really appreciate it. I'm at a loss of where to go
from here except to start truncating the lengthy varchar strings. I
don't even know if I have the option to do that.
Aaron

generate three rows out of one row from a table

i've two tables as shown below, i want to generate the table2 using table1,
more details -- for each in table1 i want three rows in table2 these three
rows col2 says A, B, C
table1
--
c1
--
1
2
3
table2
--
c1 c2
-- --
1 A
1 B
1 C
2 A
2 B
2 C
3 A
3 B
3 C
thanks in advanceUse a cartesian product -- CROSS JOIN in SQL.
SELECT c1, c2
FROM table1
CROSS JOIN ( SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C' ) D ( c2 ) ;
Anith

Generate Report Query

Hi every body
the image below describe entity relation diagram of my database i need to Write Query the generate report of licensed buildings (that has license saved in BuildingLicense Table ) and not licensed buildings in section or street

i need to gerate report like table below

select
B.streat,
Licensed = sum(case when BL.LicNo is not null then 1 else 0 end),
NotLicensed = sum(case when BL.LicNo is null then 1 else 0 end)
from
Building B (nolock)
left outer join BuildingLicensce BL (nolock)
on BL.Building_NO = B.BuildingNo
group by
B.streat

if you also what the Serial Number column, create a temp table with identity columns and dump above results intot the temp table.

|||Thanks MR

Lakshmana Kumar K for your interest
i tried you query
in my databast i stored 5 records in buildings table and when i run query i have this result

in result the sum of licensed and not licensed buildings give the total number of buildings
in your query it gives 8 Why?

I understoond your Query
in your Query u get the count of Licensecs for buildings in the street
but i just need the number of buildings which have license Record not the Count of Licenses
|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]
Thanks MR

Lakshmana Kumar K 4 your Interest

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comHi,
The Query can be re-written as:
=============================ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via SQLMonster.com" wrote:
> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
--
Message posted via http://www.sqlmonster.com|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via SQLMonster.com" wrote:
> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.sqlmonster.com
>