RE: SQL Cast Question

Jay,

If you can come up with some sample data in which you would like to group by on count this may help explain why you'd like that.


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay Vaughn
Sent: Monday, February 25, 2019 8:12 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Cast Question

Rob. See hausers response to not allowed in group by.

Jay

On Feb 25, 2019, at 7:12 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

Jay,

Let's make it simple.
Select count(*) as TheCount
From mytable
Group by TheCount;

Does that make sense?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Jay Vaughn
Sent: Monday, February 25, 2019 6:57 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Cast Question

Yuk Birgitta, why?

Why wasn’t it made allowable in the group by? Do you know?

Jay

On Feb 25, 2019, at 1:05 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Using the given name will work for the ORDER BY clause, but NOT for the group by clause.
In the group by you need to repeat the expression used in the SELECT ... row.

... and aggregate functions cannot be used within the WHERE clause, but must be specified within the HAVING clause.

Example:
Select Fld1, Fld2, Cast(Count(Fldat#) as Dec(7, 0)) as Counter
From YourTable ....
Where Fldx = 'A' and FLDy = 12345
Group By Fld1, Fld2
Having Cast(Count(Flat#) as Dec(7, 0)) > 1000,0
Order By Counter Desc;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown) "If you think education is expensive, try ignorance."
(Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough
so they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Jay Vaughn
Sent: Montag, 25. Februar 2019 03:15
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Cast Question

cast(count(fdflat#) as decimal(7,0)) as “myCount”

Group by myCount
Order by myCount.

I think this works.

Jay

On Feb 24, 2019, at 9:01 PM, Art Tostaine, Jr. <atostaine@xxxxxxxxx> wrote:

cast(count(fdflat#) as decimal(7,0)) '

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com