RE: SQL Cast Question

Ok, let's break this down
Item, iclass
A, 1
B, 1
C, 1
D, 2
E, 3
F, 3
G, 4
select iclass
,count(*) as myCount
from classTable
group by iclass
order by iclass
Would return
Iclass, count
1, 3
2, 1
3, 2
4, 1
What would you expect
select iclass
,count(*) as myCount
from classTable
group by iclass
,myCount
order by iclass
,myCount
To return?




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

same reason you'd want to do a group by on any other non-aggregate column...

select iclass
,count(*) as myCount
from classTable
group by iclass
,myCount
order by iclass
,myCount

want to see distinct classes and their respective counts... though you don't want counts for any classes repeated

what is so forbidden about aggregate data in group by?

Jay


On Mon, Feb 25, 2019 at 8:27 AM Rob Berendt <rob@xxxxxxxxx> wrote:

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
--
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