Counting in SQL

[Layth]

I have an SQL statement that counts by field AMPORDERFK and groups by fields ALTACCTNBR, SLUGLINE, and BASEAMOUNT. While this part works great, I want to know how to insert the rest of my fields into the query without them grouping the count. If I include them in the select statement I get an error. Below is the code for what I have working and code for what I think is right, as well as the error.


The correct code

SELECT [Folio Sepation by rate].ALTACCTNBR,  [Folio Sepation by rate].BASEAMOUNT, [Folio Sepation by rate].SLUGLINE,
Count([Folio Sepation by rate].AMPORDERFK) AS CountofAMPORDERFK
FROM [Folio Sepation by rate]
Group BY [Folio Sepation by rate].ALTACCTNBR, [Folio Sepation by rate].BASEAMOUNT, [Folio Sepation by rate].SLUGLINE;

The code with added fields under select:

SELECT [Folio Sepation by rate].ALTACCTNBR,  [Folio Sepation by rate].BASEAMOUNT, [Folio Sepation by rate].SLUGLINE,[Folio Sepation by rate].REALLINES, [Folio Sepation by REALCOLS,
Count([Folio Sepation by rate].AMPORDERFK) AS CountofAMPORDERFK
FROM [Folio Sepation by rate]
Group BY [Folio Sepation by rate].ALTACCTNBR, [Folio Sepation by rate].BASEAMOUNT, [Folio Sepation by rate].SLUGLINE;

The error that the query throws:

"You tried to execute a query that does not include the specified expression 'REALLINES' as part of an aggregate function."


Any help with this will be greatly appreciated.

thanks,

Layth

 

[SkipVought]

Hi,

SELECT A.ALTACCTNBR,  A.BASEAMOUNT, A.SLUGLINE,A.REALLINES, A.REALCOLS,
Count(A.AMPORDERFK) AS CountofAMPORDERFK
FROM [Folio Sepation by rate] A
Group BY A.ALTACCTNBR,  A.BASEAMOUNT, A.SLUGLINE,A.REALLINES, A.REALCOLS;

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[Layth]

Yes SkipVought I understand that I can get past the error by coding this way, but I don't actually wan't to group by A.REALLINES or A.REALCOLS, I just wan't them included, If I use them in the group by, it dilutes the count down to 1 piece because they are unique values.

Please post any questions that you may have,

Thanks for the help,

Layth

 

[SkipVought]

Then use an aggregator on each of those like Max or FIRST.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[lespaul]

I understand that I can get past the error by coding this way, but I don't actually wan't to group by A.REALLINES or A.REALCOLS

That's not "getting past the error", that's the way aggregate functions work, you have to group by all the non-aggregate fields in the SELECT statement.

Since there are apparently different values in REALLINES for each instance of ALTACCTNBR and BASEAMOUNT, which ONE do you want to return with this query? As Skip indicated, you have to identify which ONE to return.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Layth]

Yes, but I actually need to have the original data for REALLINES and REALCOLS returned as well, If I do a group by the first(REALLINES)I will get a correct count, but that will not return the data I need for REALLINES, isn't there a way to do a count with fields ALTACCTNBR, BASEAMOUNT, SLUGLINE in the Group By, and then just append fields REALLINES and REALCOLS with their original data from the table.

 

[lespaul]

again, how do you determine which records contain the "original" data for those fields?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Layth]

I'm not sure if I'm understanding you correctly, all of the records are going to need to be returned with its original form. For each ALTACCTNBR there are multiple AMPORDERFK and for each AMPORDERFK there is a certain rate charged, I need to know the count for AMPORDERFK so I know how many times to charge that person, but I need all of the fields to be returned as well. The end product should look just like the beginning, but with an added field on each record that records the number of records that the same AMPORDERFK is shown for a particular ALTACCTNBR.

And now that I look at it AMPORDERFK never repeats itself, so I don't even need ALTACCTNBR to group by, what I need to know is that in this table is how many times a record specific AMPORDERFK appears and I need that in a separate field.

 

[lespaul]

Let's start over. You have a table? query? named:

[Folio Sepation by rate]

If a table, there are the following fields:

ALTACCTNBR
BASEAMOUNT
SLUGLINE
REALLINES
REALCOLS
AMPORDERFK

The data in this table is like this:

ALTACCTNBR BASEAMOUNT SLUGLINE REALLINES REALCOLS AMPORDERFK

please insert some sample data

Using the data above I would like to have a query return the following information:

please insert the results of the query based on the information above

By providing this information and allowing us to "see" what you are seeing and what you want to accomplish, you will most likely get exactly what you need.

 

[SkipVought]

You can't have it both ways.

" I don't actually wan't to group by A.REALLINES or A.REALCOLS, I just wan't them included, If I use them in the group by, it dilutes the count down to 1 piece because they are unique values."

If, within the GROUP, there are MULTIPLE VALUES of either REALLINES or REALCOLS, then.....

what "original" data of these multiple values should appear?

[red]FIRST, LAST, MIN, MAX??????????[/red]





Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[Layth]

That's what I wanted to know, if you could do it both ways, I'm going to try something else and see if that works.

Thanks to both of you for trying to help me.

Layth

 

[Golom]

Give something like this a try

SELECT DISTINCT A.ALTACCTNBR, A.BASEAMOUNT, A.SLUGLINE, A.REALLINES, A.REALCOLS,

(Select Count(*) 
 From [Folio Sepation by Rate] As X
 Where    X.ALTACCTNBR = A.ALTACCTNBR 
      AND X.BASEAMOUNT = A.BASEAMOUNT 
      AND X.SLUGLINE   = A.SLUGLINE)  AS CountofAMPORDERFK

FROM [Folio Sepation by Rate] As A;

 

[PHV]

You may try this:
SELECT A.ALTACCTNBR, A.BASEAMOUNT, A.SLUGLINE, A.REALLINES, A.REALCOLS, Count(*) AS CountofAMPORDERFK
FROM [Folio Sepation by rate] AS A INNER JOIN (
SELECT ALTACCTNBR, BASEAMOUNT, SLUGLINE FROM [Folio Sepation by rate] GROUP BY ALTACCTNBR, BASEAMOUNT, SLUGLINE
) AS B ON A.ALTACCTNBR=B.ALTACCTNBR AND A.BASEAMOUNT=B.BASEAMOUNT AND A.SLUGLINE=B.SLUGLINE
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886