Re: SQL Statement and Grouping for VB/Excel Project

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: DavidM (spam_at_spam.net)
Date: 01/04/05 

Date: Tue, 4 Jan 2005 10:15:47 -0600

Thanks -- right now, QueryAnalyzer is my only front-end program.

I tried converting to Decimal/Numeric, but there was little change in
output. Don't want to round numbers.

Guess I'll have to live with it for now.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ePzORQn8EHA.3908@TK2MSFTNGP12.phx.gbl...
> You have several alternatives:
> 1. Use the ROUND function on the result
> 2. Cast to decimal/numeric instead of float
> 3. format the results in the client
>
> Bob Barrows
> DavidM wrote:
>> I redid the SQL to:
>>
>> CAST(SUM(MinsDown) As Float) / CAST(Sum(ExpectedMinsUp) As Float)
>> * 100
>>
>> I hope this is correct - it ran.
>>
>> The results are correct... but I'm getting too many decimals... For
>> example, I'm getting:
>>
>> 0.1483420593396822373 instead of 0.14 or simply .14.
>>
>> What can I do to format this better?
>>
>>
>> Here is my revised SQL:
>>
>> SELECT [DB]
>> ,Year(ProcessDate) AS Year
>> ,CASE Month(ProcessDate)
>> WHEN 1 THEN 'JAN'
>> WHEN 2 THEN 'FEB'
>> WHEN 3 THEN 'MAR'
>> WHEN 4 THEN 'APR'
>> WHEN 5 THEN 'MAY'
>> WHEN 6 THEN 'JUN'
>> WHEN 7 THEN 'JUL'
>> WHEN 8 THEN 'AUG'
>> WHEN 9 THEN 'SEP'
>> WHEN 10 THEN 'OCT'
>> WHEN 11 THEN 'NOV'
>> WHEN 12 THEN 'DEC'
>> ELSE 'UNKNOWN'
>> END AS ProcessMonth
>> ,SUM(ExpectedMinsUp) AS TotalExpectedMinsUp
>> ,SUM(MinsDown) AS TotalMinsDown
>> ,CASE WHEN SUM(MinsDown) > 0 THEN
>> CAST(SUM(MinsDown) AS Float) / CAST(SUM(ExpectedMinsUp) As
>> Float) * 100
>> ELSE
>> 0
>> END
>> FROM tblDowntime2
>> GROUP BY [DB], Year(ProcessDate), Month(ProcessDate) --WITH CUBE
>> ORDER BY Month(ProcessDate), [DB]
>>
>>
>>
>>
>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
>> news:OTKQUGn8EHA.2452@TK2MSFTNGP14.phx.gbl...
>>> You need to explicitly cast the operands to decimal or float to
>>> prevent integer division:
>>>
>>> CAST(TotalExpectedMinsUp AS float)
>>>
>>> Bob Barrows
>>>
>>> DavidM wrote:
>>>> Bob - I'm trying to do the division like you demonstrated below,
>>>> example #2 using the CASE... and it isn't working correctly.
>>>>
>>>> 1) There is no column name. I tried putting an "AS" after the
>>>> SUM(TotalMinsDown)/SUM(TotalExpectedMinsUp) called "Percent" and it
>>>> didn't like that.
>>>>
>>>> 2) I had to change the field names within
>>>> SUM(TotalMinsDown)/SUM(TotalExpectedMinsUp) to use the real field
>>>> names instead of the alias? I thought I could use the alias. Guess
>>>> not.
>>>>
>>>> 3) The output for the column is basically 0 or 1. For example,
>>>> 24210 / 24210 = 1. Another 55 / 8310 = 0. I then tried
>>>> multiplying by 100 and I now get 0 or 100. I'm guessing I got to
>>>> convert this to a Decimal or something???
>>>>
>>>> Any help would be appreciated.
>>>>
>>>>
>>>>
>>>>
>>>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
>>>> news:uyJlTXm8EHA.2156@TK2MSFTNGP10.phx.gbl...
>>>>> Same SELECT statement, just add:
>>>>>
>>>>> SELECT ...,
>>>>> SUM(TotalMinsDown)/SUM(TotalExpectedMinsUp)
>>>>> FROM ...
>>>>>
>>>>> to the SELECT clause. You may want to use CASE to make it a little
>>>>> more error-proof:
>>>>>
>>>>> SELECT ...,
>>>>> CASE WHEN SUM(TotalExpectedMinsUp) > 0 THEN
>>>>> SUM(TotalMinsDown)/SUM(TotalExpectedMinsUp)
>>>>> ELSE
>>>>> 0
>>>>> END
>>>>> FROM ...
>>>>>
>>>>> Bob Barrows
>>>>> DavidM wrote:
>>>>>> Thanks everyone -- exactly what I needed.
>>>>>>
>>>>>> How can I compute the percentage from TotalMinsDown /
>>>>>> TotalExpectedMinsUp within the SELECT?
>>>>>>
>>>>>>
>>>>>>
>>>>>> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
>>>>>> news:e5ijt0h4gouufkvrjnmkq5amfih8q7ort8@4ax.com...
>>>>>>> On Mon, 3 Jan 2005 16:05:52 -0600, DavidM wrote:
>>>>>>>
>>>>>>>> I have a table that contains information that I would like
>>>>>>>> grouped by month
>>>>>>>> and name along with a total based on a table value, if possible.
>>>>>>>> I'm not sure how to do this.
>>>>>>> (snip)
>>>>>>>> How would I write this SQL for SQL Server?
>>>>>>>
>>>>>>> Hi David,
>>>>>>>
>>>>>>> Try running this query:
>>>>>>>
>>>>>>> SELECT DB, MONTH(ProcessDate) AS ProcessMonth,
>>>>>>> SUM(ExpectedMinsUp) AS TotalExpectedMinsUp,
>>>>>>> SUM(MinsDown) AS TotalMinsDown
>>>>>>> FROM MyTable
>>>>>>> GROUP BY DB, MONTH(ProcessDate)
>>>>>>>
>>>>>>>
>>>>>>>> Maybe as another item, how can I SUM the TotalExpectedMinsUp and
>>>>>>>> TotalMinsDown for all [DB] for the entire month grouped by
>>>>>>>> month?
>>>>>>>
>>>>>>> Almost the same answer:
>>>>>>>
>>>>>>> SELECT MONTH(ProcessDate) AS ProcessMonth,
>>>>>>> SUM(ExpectedMinsUp) AS TotalExpectedMinsUp,
>>>>>>> SUM(MinsDown) AS TotalMinsDown
>>>>>>> FROM MyTable
>>>>>>> GROUP BY MONTH(ProcessDate)
>>>>>>>
>>>>>>> (both above queries were untested, as you didn;t provide the SQL
>>>>>>> needed to reproduce your table and populate it with test data -
>>>>>>> see http://www.aspfaq.com/5006)
>>>>>>>
>>>>>
>>>>> --
>>>>> Microsoft MVP -- ASP/ASP.NET
>>>>> Please reply to the newsgroup. The email account listed in my From
>>>>> header is my spam trap, so I don't check it very often. You will
>>>>> get a quicker response by posting to the newsgroup.
>>>
>>> --
>>> Microsoft MVP -- ASP/ASP.NET
>>> Please reply to the newsgroup. The email account listed in my From
>>> header is my spam trap, so I don't check it very often. You will get
>>> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Relevant Pages

  • Re: SQL Statement and Grouping for VB/Excel Project
    ... I tried converting to Decimal/Numeric, but there was little change in ... Don't want to round numbers. ... >> What can I do to format this better? ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Statement and Grouping for VB/Excel Project
    ... I tried converting to Decimal/Numeric, but there was little change in ... Don't want to round numbers. ... >> What can I do to format this better? ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.vb.database)
  • Re: SQL Statement and Grouping for VB/Excel Project
    ... I tried converting to Decimal/Numeric, but there was little change in ... Don't want to round numbers. ... >> What can I do to format this better? ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.vb.general.discussion)
  • Amber might wearily fund our tone
    ... Somebody adjust once, chew occasionally, then enhance ... but the format except for the regiment. ... It will contribute round if Najem's birth isn't fantastic. ...
    (sci.crypt)
  • if the recent challenges can explore cautiously, the hollow skill may wander more mirrors
    ... Hey, it dips a composer too dreadful throughout her ... Try converting the execution's easy halt and ... It should emerge incredibly, unless Tommy supervises transports ... If you will load Hassan's workstation round accounts, ...
    (sci.crypt)