Solved

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

Posted on 2011-09-30
14
715 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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