SQL Select count

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

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

%>


Thanks for your help.

[shyam]

do you get the same count if you use

select date, count(code) from tbm_registered
where code like '123%' and q='AAA' group by date;

[Mike1961]

do you get the same count if you use

select date, count(code) from tbm_registered
where code like '123%' and q='AAA' group by date;

thanks, but the result not change:


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


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

[Mike1961]

I attach sql tables, excel files and asp page:

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

PSW the ZIP file is "forum".