Count Record in RIN1 table

[Former Member]

Hi can anybody help me with my query. I want count the Baseref field from RIN1 table, want to Count DocEntry field that have greater to 1 in the Baseref field. This query below is running :

SELECT BaseRef,

COUNT(DocEntry)

FROM

(SELECT

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry

FROM

dbo.RIN1

GROUP BY

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry) CountBaseRef

GROUP BY

BaseRef,

But when used to DECLARE command like below :

error message appears : "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

DECLARE @Counter INT

SET @Counter =

( SELECT BaseRef,

COUNT(DocEntry)

FROM

(SELECT

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry

FROM

dbo.RIN1

GROUP BY

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry) CountBaseRef

GROUP BY

BaseRef )

Please guide me thank you very much!.

Clint

[former_member583013 · Accepted solution]

Clint,

The Syntax of your Second SQL is incorrect. You are trying to assign the SQL Query sting to a variable of type INT.

Also, your first SQL can be a simple one like

SELECT T0.BaseRef, COUNT(T0.DocEntry)

FROM [dbo\].[RIN1\] T0 GROUP BY T0.BaseRef

Could you tell what you really want to accomplish through your seond SQL Query?

Suda

[Former Member · Accepted solution]

Hi Suda,

Im hoping that you will be the one to answer. What I want to the result will be is, From the RIN1 table I want to count the DocEntry field corresponding to its Baseref field, sample data table:

Baseref DocEntry

200 10

200 10

255 31

255 34

I want to count the DocEntry group by Baseref: sample base in above data:

Baseref Count DocEntry

200 1

255 2

You can see I want to determine the number of DocEntry per Baseref data. The purpose SQL Query sting to a variable of type INT is to assign the value of CountDocEntry.

Kindly guide me, thank you in advance.

Clint

[former_member583013 · Accepted solution]

Clint,

I have given you the answer. Please test the query I gave you in my previous reply

Suda

[Former Member · Accepted solution]

Thank you Suda for your prompt reply, I guess my question is very confusing, I have tested your query but did not met I want used to be.

I dont know if you got me, I want to count the DocEntry in the Baseref which have different DocEntry. Sample :

Baseref DocEntry

200 10

200 10

-- the result count of the DocEntry must be 1 only because it contain only the same number which is 10.

Another :

Baseref DocEntry

255 31

255 34

-- the result count of the DocEntry must be be 2 because it contains Different no. 31 and 34.

all have same Baseref number.

Hoping it will clarify it all. Thank you very much in advance Suda.

Regards,

Clint

[former_member583013 · Accepted solution]

In that case your original query itself would work fine

SELECT BaseRef,

COUNT(DocEntry)

FROM

(SELECT

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry

FROM

dbo.RIN1

GROUP BY

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry) CountBaseRef

GROUP BY

BaseRef

[Former Member · Accepted solution]

Thank you again Suda.

Is there any other way or solution that can requery this statement? without using subquery. The purpose of this is to call this query and used it DECLARE statement to detect that Baseref field have 2 records of DocEntry.

The problem is when I used DECLARE as stated in the above statement. Declare statement pop-up a message, that it cant accept subquery.

Is there any other way?

Thank you!

[former_member583013 · Accepted solution]

Why do you want to use DECLARE? What you have is perhaps a really good solution.

[Former Member · Accepted solution]

Here is the statement that I want to DECLARE but an error msg appeared:

"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

DECLARE @Counter INT

SET @Counter =

( SELECT BaseRef,

COUNT(DocEntry)

FROM

(SELECT

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry

FROM

dbo.RIN1

GROUP BY

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry) CountBaseRef

GROUP BY

BaseRef )

I want to use the @Counter from another query of mine the purpose is to detect the record that have 2 Count of Record. But it seems Im stuck in here.

I declare @Counter as INT to refer the count number.

Regards,

Clint

[Former Member · Accepted solution]

The reason is, that you tried to add into integer variable two columns from db.

Use it as

DECLARE @Counter INT

SET @Counter =

( SELECT COUNT(DocEntry)

FROM

(SELECT

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry

FROM

dbo.RIN1

GROUP BY

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry) CountBaseRef

GROUP BY

BaseRef )

But dont forgett to eliminate the records only for one record through docentry. Otherwise the result of query isnt one record and you cannot set it to integer variable.

Or use SELECT TOP 1 or cursors.

[Former Member · Accepted solution]

Hi there,

Still wont work here the message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Cant figure it out! dont know how to solve the problem!

Clint

[Former Member · Accepted solution]

Yes, I wrote it to you. You must eliminate the select for one record.

Or use tmp table and make select from this table as

DECLARE @Counter INT

( SELECT BaseRef, COUNT(DocEntry) as cn

into #tmp

FROM

(SELECT

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry

FROM

dbo.RIN1

GROUP BY

dbo.RIN1.BaseRef,

dbo.RIN1.DocEntry) CountBaseRef

GROUP BY

BaseRef )

SET @Counter =

(select cn from #tmp where baseref = XXX)