Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 766
  • Last Modified:

T-SQL How to Divide by COUNT without Doing a Group By

Hello:

Attached are two select queries.  The first long query is the one that I need help with.  The second short query is a "select COUNT" statement that I need to embed in my long query.  Let me explain.

The short query is as follows:  select COUNT (aaSubLedgerHdrID) from AAG20003.  In my long query, I have the following:  OAKVA..AAG20001.DEBITAMT as [Debit].

I want to take this Debit field and divide it by the COUNT "amount".  In my example, the Debit amount is 2400 and the COUNT is 6.  So, I should get 400.

After trying this and after running my long query, however, I get the following error:
Column 'OAKVA..GL20000.JRNENTRY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I am not so sure that I want to do a bunch of GROUP BYs for my query.  I simply want to divide that Debit amount by that COUNT amount.

Is there any special syntax that I can use to accomplish what should be a simple feat?

Thanks, and much appreciated!

Apitech
select DISTINCT --OAKVA..AAG00401.aaTrxDimCode, OAKVA..AAG00401.aaTrxDimCodeDescr,
OAKVA..GL20000.JRNENTRY as [DocumentNumber], OAKVA..PM30200.DOCDATE as [DocumentDate], OAKVA..GL20000.TRXDATE as [PostDate], 
LEFT(OAKVA..RM00101.CUSTNMBR, 3) as [ClientCode], OAKVA..RM00101.CUSTNMBR as [ClientNumber], 
OAKVA..RM00101.CUSTNAME as [ClientName],
OAKVA..GL20000.SOURCDOC as [DocumentType],
0 as [Sales], 0 as [Tax], 
0 as [Total1], 
OAKVA..GL20000.TRXSORCE as [Source], 
OAKVA..GL00105.ACTNUMST as [PostingAccount], 
RIGHT(rtrim(OAKVA..GL00105.ACTNUMST), 6) as [NaturalAccount], OAKVA..GL00100.ACTDESCR as [Description], 
--OAKVA..ME50405.USERDEF1 as [PropertyManagement], 
OAKVA..AAG20001.DEBITAMT as [Debit], OAKVA..AAG20001.CRDTAMNT as [Credit], 
OAKVA..AAG20001.CRDTAMNT - OAKVA..AAG20001.DEBITAMT as [Total2]
from OAKVA..AAG00401
INNER JOIN OAKVA..AAG20003 on OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
INNER JOIN OAKVA..AAG20001 on OAKVA..AAG20001.aaSubLedgerHdrID = OAKVA..AAG20003.aaSubLedgerHdrID
and OAKVA..AAG20001.aaSubLedgerDistID = OAKVA..AAG20003.aaSubLedgerDistID
INNER JOIN OAKVA..AAG20000 on OAKVA..AAG20001.aaSubLedgerHdrID = OAKVA..AAG20000.aaSubLedgerHdrID
INNER JOIN OAKVA..GL20000 on OAKVA..AAG20000.DOCNUMBR = OAKVA..GL20000.ORCTRNUM
INNER JOIN OAKVA..PM30200 on OAKVA..GL20000.ORCTRNUM = OAKVA..PM30200.VCHRNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..RM00101 on OAKVA..AAG00401.aaTrxDimCode = OAKVA..RM00101.CUSTNMBR
INNER JOIN OAKVA..ME50405 on OAKVA..RM00101.CUSTNMBR = OAKVA..ME50405.CUSTNMBR
where OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
--and OAKVA..AAG20003.aaSubLedgerHdrID = '322047'
and SUBSTRING(OAKVA..GL00105.ACTNUMST, CHARINDEX('-', ACTNUMST) + 1, 1) = '4'
--and OAKVA..AAG20000.SERIES = '4'
--and OAKVA..AAG20001.aaCustID = '' and OAKVA..AAG20001.aaVendID <> ''
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' and '2011-08-31 00:00:00.000'
and OAKVA..GL20000.SOURCDOC = 'PMTRX'


select COUNT (aaSubLedgerHdrID) from AAG20003

Open in new window

0
apitech
Asked:
apitech
  • 6
  • 4
  • 3
  • +1
1 Solution
 
CrashmanCommented:
you can use this
SELECT  it.ColumnA,
              COUNT(it.ColumnB) OVER ( PARTITION BY it.ColumnA)
FROM    Table it

Open in new window


But, can you how the query complete, include the second query.
0
 
apitechAuthor Commented:
I'm sorry, but I don't follow you.  For one thing, what field am I supposed to partition over?  I'm unclear.

Below is what I am trying to do for the Debit field, if that helps:

OAKVA..AAG20001.DEBITAMT/(select COUNT (OAKVA..AAG20003.aaSubLedgerHdrID)) as [Debit]

Apitechj
0
 
CrashmanCommented:
http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx
http://msdn.microsoft.com/en-us/library/ms189461.aspx


in this query

select COUNT (OAKVA..AAG20003.aaSubLedgerHdrID)

you ALLWAYS get the same number, is this correct? or the count is by some field?
you can use a subquery relations

Select id,  a / b from  tableA a inner join ( select id, count(b)  from tableB group by id ) b on a.id = b.id
in the count you must use GROUP BY for every field without agregate (sum,count,avg,etc)
in the Partition its the same, partition create that, a partition and use the agregate for every partition,

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Leo TorresSQL DeveloperCommented:
try this
Declare @debit int

IF OBJECT_ID('tempdb..#tempTable')
	Drop Table #tempTable
select DISTINCT --OAKVA..AAG00401.aaTrxDimCode, OAKVA..AAG00401.aaTrxDimCodeDescr,
OAKVA..GL20000.JRNENTRY as [DocumentNumber], 
OAKVA..PM30200.DOCDATE as [DocumentDate], 
OAKVA..GL20000.TRXDATE as [PostDate], 
LEFT(OAKVA..RM00101.CUSTNMBR, 3) as [ClientCode], 
OAKVA..RM00101.CUSTNMBR as [ClientNumber], 
OAKVA..RM00101.CUSTNAME as [ClientName],
OAKVA..GL20000.SOURCDOC as [DocumentType],
0 as [Sales], 0 as [Tax], 
0 as [Total1], 
OAKVA..GL20000.TRXSORCE as [Source], 
OAKVA..GL00105.ACTNUMST as [PostingAccount], 
RIGHT(rtrim(OAKVA..GL00105.ACTNUMST), 6) as [NaturalAccount], 
OAKVA..GL00100.ACTDESCR as [Description], 
--OAKVA..ME50405.USERDEF1 as [PropertyManagement], 
OAKVA..AAG20001.DEBITAMT as [Debit], OAKVA..AAG20001.CRDTAMNT as [Credit], 
OAKVA..AAG20001.CRDTAMNT - OAKVA..AAG20001.DEBITAMT as [Total2],
10 as CalculationColumn 
Into #tempTable
from OAKVA..AAG00401
INNER JOIN OAKVA..AAG20003 on OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
INNER JOIN OAKVA..AAG20001 on OAKVA..AAG20001.aaSubLedgerHdrID = OAKVA..AAG20003.aaSubLedgerHdrID
and OAKVA..AAG20001.aaSubLedgerDistID = OAKVA..AAG20003.aaSubLedgerDistID
INNER JOIN OAKVA..AAG20000 on OAKVA..AAG20001.aaSubLedgerHdrID = OAKVA..AAG20000.aaSubLedgerHdrID
INNER JOIN OAKVA..GL20000 on OAKVA..AAG20000.DOCNUMBR = OAKVA..GL20000.ORCTRNUM
INNER JOIN OAKVA..PM30200 on OAKVA..GL20000.ORCTRNUM = OAKVA..PM30200.VCHRNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..RM00101 on OAKVA..AAG00401.aaTrxDimCode = OAKVA..RM00101.CUSTNMBR
INNER JOIN OAKVA..ME50405 on OAKVA..RM00101.CUSTNMBR = OAKVA..ME50405.CUSTNMBR
where OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
--and OAKVA..AAG20003.aaSubLedgerHdrID = '322047'
and SUBSTRING(OAKVA..GL00105.ACTNUMST, CHARINDEX('-', ACTNUMST) + 1, 1) = '4'
--and OAKVA..AAG20000.SERIES = '4'
--and OAKVA..AAG20001.aaCustID = '' and OAKVA..AAG20001.aaVendID <> ''
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' and '2011-08-31 00:00:00.000'
and OAKVA..GL20000.SOURCDOC = 'PMTRX'

select @Debit = COUNT (aaSubLedgerHdrID) from AAG20003

Update #tempTable
SET CalculationColumn = [Total2] / @Debit

Select * from #tempTable

Open in new window

0
 
apitechAuthor Commented:
Crashman:

Your method seems the closest to what I need.  But, I still don't understand.

I understand the "GROUP BY".  In fact, I just did it and that part was easy.

But, what do I partition over?  Please let me know.  I'm a novice at this and on a time crunch.

I do appreciate the help that you have given.   But, I need just a little more help.

Thanks!

Apitech
0
 
apitechAuthor Commented:
Let me see if I can explain what I need better.  That's the problem.

The table AAG20003 has 6 rows.  At least, in my example it does.  Next time, it may 1, it may have 2, 1976, 2092, it doesn't matter.

I want to divide the debit amount by the number of rows returned from the AAG20003 table.

That's all.  I can't believe that this is that difficult.  

Apitech
0
 
CrashmanCommented:
its not difficult, the partition by , create a gruops of values before apply the aggregate
review the link

http://msdn.microsoft.com/en-us/library/ms189461.aspx
0
 
CrashmanCommented:
For that i ask, you must create a subquery with the common column take the sum of the value of the first query like the last example, and in the internal query you make the sum and with the join make the union and put the field in the first query or simple sum
0
 
apitechAuthor Commented:
Again, what do I partition over?

Still unclear.......

Apitech
0
 
Kevin CrossChief Technology OfficerCommented:
You do NOT partition over anything in that case. You want just:

COUNT([OAKVA]..[AAG20003.aaSubLedgerHdrID]) OVER()

If you are going to use the windowing functions for this and it appears to fit given you are not doing other aggregation to be able to use conditional aggregates.
0
 
Kevin CrossChief Technology OfficerCommented:
But as stated, what you have here http:#36894194 may be what you need if the number of rows from the current query is not necessarily equal to the count of all rows in AAG20003.

You were very close, you just need to specify the FROM and understand it is a whole new query.

(SELECT COUNT(aaSubLedgerHdrID) FROM OAKVA..AAG20003)

Another way to do this would be to use an OUTER APPLY using same query as above.

e.g., starting with this:
FROM OAKVA..AAG00401
INNER JOIN OAKVA..AAG20003 on OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
...
WHERE OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
...

Open in new window


You can change to this:
FROM OAKVA..AAG00401
INNER JOIN OAKVA..AAG20003 on OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
...
OUTER APPLY (SELECT COUNT(aaSubLedgerHdrID) CntSL FROM OAKVA..AAG20003) AS C
WHERE OAKVA..AAG00401.aaTrxDimCodeID = OAKVA..AAG20003.aaTrxCodeID
...

Open in new window


Then in your code, you can do division as:

OAKVA..AAG20001.DEBITAMT / C.CntSL

Hope that helps!
0
 
apitechAuthor Commented:
Thanks!  I figured it out as follows:

OAKVA..AAG20001.DEBITAMT/
(COUNT (OAKVA..AAG20003.aaTrxCodeID) OVER(PARTITION BY OAKVA..AAG20003.aaSubLedgerHdrID))
as [Debit]
0
 
Kevin CrossChief Technology OfficerCommented:
Wonderful! I am glad. If you did indeed need to have different counts based on PARTITION BY versus my understanding that you wanted all rows, then you may want to give points to the first comment that showed you that syntax. A Moderator can assist you with that whenever you need it in the future by the way. :)

Best regards and happy coding,
Kevin
0
 
apitechAuthor Commented:
Thank you, Kevin!

Apitech
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now