Solved

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

Posted on 2011-09-30
14
698 Views
Last Modified: 2012-05-12
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
Comment
Question by:apitech
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 8

Expert Comment

by:Crashman
ID: 36894140
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
 
LVL 1

Author Comment

by:apitech
ID: 36894194
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
 
LVL 8

Expert Comment

by:Crashman
ID: 36894233
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
 
LVL 8

Expert Comment

by:Leo Torres
ID: 36894269
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
 
LVL 1

Author Comment

by:apitech
ID: 36894707
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
 
LVL 1

Author Comment

by:apitech
ID: 36894744
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
 
LVL 8

Expert Comment

by:Crashman
ID: 36894783
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:Crashman
ID: 36894791
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
 
LVL 1

Author Comment

by:apitech
ID: 36894860
Again, what do I partition over?

Still unclear.......

Apitech
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36896623
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36896640
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
 
LVL 1

Author Comment

by:apitech
ID: 36897122
Thanks!  I figured it out as follows:

OAKVA..AAG20001.DEBITAMT/
(COUNT (OAKVA..AAG20003.aaTrxCodeID) OVER(PARTITION BY OAKVA..AAG20003.aaSubLedgerHdrID))
as [Debit]
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36897222
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
 
LVL 1

Author Comment

by:apitech
ID: 36898252
Thank you, Kevin!

Apitech
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now