Select Count SQL

[Mike1961]

Hi there.

Well the situation is this, I have two tables:

1) tbl_registry where rows with code = 123 are 35 ;
1) tbl_Registered where rows with code = 123 are 43

In tbl_Registered I have it 7 rows more respect tbl_registry can you tell my why I have this situation:

ID = 1
NAME = JOHN
DATE = 2008-04-08
CODE = 123
Q = AAA
ACTIVITIES = XYZ
HOURS = 2

ID = 2
NAME = JOHN
DATE = 2008-04-08
CODE = 123
Q = AAA
ACTIVITIES = ABC
HOURS = 3

ID = 3
NAME = JIM
DATE = 2008-04-08
CODE = 123
Q = AAA
ACTIVITIES = XYZ
HOURS = 5

In tbl_Registered the rows is perfectly equal EXCEPT for the field HOURS and the field ACTIVITIES:

That is JOHN today 2008-04-08 has divided the total of 5 hours in two pieces of activity (2 -XYZ- and the other from 3-ABC-), while JIM has concentrated the total 5 hours only activity ABC.

I would like to query data extracts are the same:

Tot tbl_Registry = 35 ===> is right
Tot tbl_Registered = 35 ===> is right

Tot tbl_Registry = 35 ===> is right
Tot tbl_Registered = 43 ===> is wrong

My queries:

[php]

<%
sql_count = " SELECT "
sql_count = sql_count & " COUNT(CODE) "
sql_count = sql_count & " FROM "
sql_count = sql_count & " tbl_registry "
sql_count = sql_count & " WHERE "
sql_count = sql_count & " CODE LIKE '123%' "
sql_count = sql_count & " AND "
sql_count = sql_count & " Q = 'AAA' "

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql_count, cn

response.write objRS(0) & "<br>"

objRS.Close
Set objRS = Nothing

sql = " SELECT"
sql = sql & " DATE,"
sql = sql & " CODE,"
sql = sql & " Q,"
sql = sql & " COUNT(CODE) AS tot"
sql = sql & " FROM "
sql = sql & " tbl_Registered "
sql = sql & " WHERE "
sql = sql & " CODE LIKE '123%'"
sql = sql & " AND DATE = '2008-04-08'"
sql = sql & " AND Q = 'AAA'"
sql = sql & " GROUP BY "
sql = sql & " DATE "

Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sql, cn

response.write RS("tot") & "<br>"

RS.Close
Set RS = Nothing

cn.Close
Set cn = Nothing

%>
[/php]

Thanks for your help.

[jeffstl]

Maybe I am misunderstanding, but you want the count of records in your table with code 123 then why do you use the SQL LIKE syntax?

This will pull records with the code 1234, or 1230000, etc. Or possibly even 2123. Anything with the 123 sequence will be pulled as part of your count with the LIKE in there.

I might have missed a reason for it in your description of the problem though, if so please point it out.

If you want this to pull strictly code 123 just use the = sign instead of like and remove the %.

<%
sql_count = " SELECT "
sql_count = sql_count & " COUNT(CODE) "
sql_count = sql_count & " FROM "
sql_count = sql_count & " tbl_registry "
sql_count = sql_count & " WHERE "
sql_count = sql_count & " CODE = '123' "
sql_count = sql_count & " AND "
sql_count = sql_count & " Q = 'AAA' "
 
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql_count, cn
 
 

Another suggestion would be to print out your rows on your page (just for testing purposes) and see what records are actually being pulled that should not be. This will help narrow down the reason for the incorrect COUNT.

[viki1967]

... sorry i wrong post...

[Mike1961]

Hi.

Thanks for your answer, but the problem is more difficult... I do not explain you...

I attach sql tables, excel files and ASP page:

http://www11.asphost4free.com/Miguel61/r937.zip

PSW the ZIP file is forum.

This file ZIP is not contagious or dangerous.... :)

Please help my !!!

I am desperate case.... jejejeje

Regards.

[DrBunchman]

Hi Mike1961,

I don't understand what it is you're trying to do! Please explain it again and try to be clearer.

Don't be afraid of giving too much information about your problem - that's better than too little!

Dr B

[Mike1961]

Hi Mike1961,

I don't understand what it is you're trying to do! Please explain it again and try to be clearer.

Don't be afraid of giving too much information about your problem - that's better than too little!

Dr B

Hi DrBunchman and thanks for your answer.

It has been difficult for me, but this is the solution:

<%
 
sDatabaseConnection = "DRIVER={MySQL ODBC 3.51 Driver};"_
                            & "SERVER=localhost;"_
                            & "DATABASE=test;"_
                            & "UID=root;PWD=XXXX; OPTION=35;"
 
Set connCount = Server.Createobject("ADODB.Connection")
connCount.open sDatabaseConnection        
 
sql_count = " SELECT "
sql_count = sql_count & " COUNT(CODE) "
sql_count = sql_count & " FROM "
sql_count = sql_count & " tbl_registry "
sql_count = sql_count & " WHERE "
sql_count = sql_count & " CODE LIKE '9168383%' "
sql_count = sql_count & " AND "
sql_count = sql_count & " Q = 'AAA' "
 
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql_count, connCount
 
    response.write sql_count & "<br><br>"
 
if not objRS.eof then
 
sql = " SELECT "
sql = sql & " NAME "
sql = sql & " FROM " 
sql = sql & " tbl_Registered "
sql = sql & " WHERE "
sql = sql & " CODE LIKE '9168383%'"
sql = sql & " AND DATE = '2008-04-08'"
sql = sql & " AND Q = 'AAA'"
sql = sql & " GROUP BY "
sql = sql & " NAME "
 
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sql, connCount
 
response.write sql & "<br><br>"
 
if not Rs.eof then
 
count = 0
 
Rs.MoveFirst()
Do While Not Rs.EOF
 
    response.write Rs("NAME") &"<br>"
 
count = count + 1
 
Rs.MoveNext()
Loop
 
   response.write "<br> Totali tbl_registered = " & count & "<br>"
   response.write "Tot tbl_Registry = " & objRS(0) & "<br>"
 
end if
end if 
 
objRS.Close
Set objRS = Nothing
 
RS.Close
Set RS = Nothing
 
connCount.Close
Set connCount = Nothing 
 
%>
 

I have:

Totali tbl_registered = 2 ===> right
Tot tbl_Registry = 35 ===> right

Try this code, pls....

Regards
Mike

[DrBunchman]

Hi Mike, have you resolved this yet?

[Mike1961]

Hi Mike, have you resolved this yet?

Yes, I resolved.
thanks