470,573 Members | 1,769 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,573 developers. It's quick & easy.

Select Count SQL

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.
Apr 8 '08 #1
7 3682
jeffstl
432 Expert 256MB
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 %.

Expand|Select|Wrap|Line Numbers
  1. <%
  2. sql_count = " SELECT "
  3. sql_count = sql_count & " COUNT(CODE) "
  4. sql_count = sql_count & " FROM "
  5. sql_count = sql_count & " tbl_registry "
  6. sql_count = sql_count & " WHERE "
  7. sql_count = sql_count & " CODE = '123' "
  8. sql_count = sql_count & " AND "
  9. sql_count = sql_count & " Q = 'AAA' "
  10.  
  11. Set objRS = Server.CreateObject("ADODB.Recordset")
  12. objRS.Open sql_count, cn
  13.  
  14.  
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.
Apr 9 '08 #2
viki1967
263 100+
... sorry i wrong post...
Apr 9 '08 #3
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.
Apr 9 '08 #4
DrBunchman
979 Expert 512MB
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
Apr 10 '08 #5
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:

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. sDatabaseConnection = "DRIVER={MySQL ODBC 3.51 Driver};"_
  4.                             & "SERVER=localhost;"_
  5.                             & "DATABASE=test;"_
  6.                             & "UID=root;PWD=XXXX; OPTION=35;"
  7.  
  8. Set connCount = Server.Createobject("ADODB.Connection")
  9. connCount.open sDatabaseConnection        
  10.  
  11. sql_count = " SELECT "
  12. sql_count = sql_count & " COUNT(CODE) "
  13. sql_count = sql_count & " FROM "
  14. sql_count = sql_count & " tbl_registry "
  15. sql_count = sql_count & " WHERE "
  16. sql_count = sql_count & " CODE LIKE '9168383%' "
  17. sql_count = sql_count & " AND "
  18. sql_count = sql_count & " Q = 'AAA' "
  19.  
  20. Set objRS = Server.CreateObject("ADODB.Recordset")
  21. objRS.Open sql_count, connCount
  22.  
  23.     response.write sql_count & "<br><br>"
  24.  
  25. if not objRS.eof then
  26.  
  27. sql = " SELECT "
  28. sql = sql & " NAME "
  29. sql = sql & " FROM " 
  30. sql = sql & " tbl_Registered "
  31. sql = sql & " WHERE "
  32. sql = sql & " CODE LIKE '9168383%'"
  33. sql = sql & " AND DATE = '2008-04-08'"
  34. sql = sql & " AND Q = 'AAA'"
  35. sql = sql & " GROUP BY "
  36. sql = sql & " NAME "
  37.  
  38. Set RS = Server.CreateObject("ADODB.Recordset")
  39. RS.Open sql, connCount
  40.  
  41. response.write sql & "<br><br>"
  42.  
  43. if not Rs.eof then
  44.  
  45. count = 0
  46.  
  47. Rs.MoveFirst()
  48. Do While Not Rs.EOF
  49.  
  50.     response.write Rs("NAME") &"<br>"
  51.  
  52. count = count + 1
  53.  
  54. Rs.MoveNext()
  55. Loop
  56.  
  57.    response.write "<br> Totali tbl_registered = " & count & "<br>"
  58.    response.write "Tot tbl_Registry = " & objRS(0) & "<br>"
  59.  
  60. end if
  61. end if 
  62.  
  63. objRS.Close
  64. Set objRS = Nothing
  65.  
  66. RS.Close
  67. Set RS = Nothing
  68.  
  69. connCount.Close
  70. Set connCount = Nothing 
  71.  
  72. %>
  73.  
I have:

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

Try this code, pls....

Regards
Mike
Apr 11 '08 #6
DrBunchman
979 Expert 512MB
Hi Mike, have you resolved this yet?
Apr 15 '08 #7
Hi Mike, have you resolved this yet?
Yes, I resolved.
thanks
Apr 16 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

21 posts views Thread by John Fabiani | last post: by
3 posts views Thread by Ian T | last post: by
4 posts views Thread by jimh | last post: by
4 posts views Thread by headware | last post: by
6 posts views Thread by Ben Hallert | last post: by
22 posts views Thread by MP | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.