Counting how many times exist in other table

Databases
1

HI, can I ask if I am doing right , I am counting licensekey or licenseregisterno that exists many times in
personal_key table.

SELECT (SELECT COUNT(*) FROM personal_key  WHERE licensekey = "$key"
           OR licenseregisterno = "$key") AS countkeys_registerno
		   FROM personal as p
		   inner join personal_key as pkeyreg
		   ON pkeyreg.id = p.personal_key_reg_id
		   where p.status IN ('valid','completed')

Thank you in advance.

2

what happened when you tested it? ™

3

HI sorry for the late reply, actually I get the correct count as what I expected, I’m just doubt of my sql if that is the right way.

Thank you

4

the best way to learn is not by asking for help, but by testing, fixing, testing again, checking the manual for the correct syntax, fixing, and testing again

after that, it’s okay to ask for help

5

The syntax of the outer query is making my head hurt though.

1 Like
6

can you please tell me why ? that’s why I am asking even though I get the right output but I know my syntax is not correct ?

7

run the subquery by itself –

SELECT COUNT(*) 
  FROM personal_key  
 WHERE licensekey = "$key"
    OR licenseregisterno = "$key"

you will get back exactly one row, containing a count

now run this query by itself –

SELECT 'oh hai' AS countkeys_registerno
  FROM personal as p
INNER 
  JOIN personal_key as pkeyreg
    ON pkeyreg.id = p.personal_key_reg_id
 WHERE p.status IN ('valid','completed')

and now please tell us how many rows of ‘oh hai’ did you get?

8

1 result ‘oh hai’

9

okay, great

now please replace ‘oh hai’ with COUNT(*)

also, how are you running these queries?

by the way, you forgot to mention what was the count from running the subquery by itself

10

I got 1 result changing to count(*)

1 ?

11

then something is really really wrong somewhere

in your first post, you said “I am counting licensekey or licenseregisterno that exists many times in personal_key table”

please tell me the count you get for this query –

SELECT COUNT(*) FROM personal

and also the count you get for this query –

SELECT COUNT(*) FROM personal_key

also, how are you running these queries? phpmyadmin? command line? something else?

12

This is my table looks like, I want to count the licenseky/licenseregisterno ,that there row id exist in
the personal table

I’m running this in SQL editor like heidiSQL.

personal_key table

column names:
id, licensekey, licenseregisterno

+---+---------+---------+
| 1 | lic-123 | reg-223 |
+---+---------+---------+
| 2 | lic-124 | reg-224 |
+---+---------+---------+
| 3 | lic-125 | reg-225 |
+---+---------+---------+
| 4 | lic-123 | reg-223 |
+---+---------+---------+

personal table

column names:
id, personal_key_reg_id, status

+---+---+-----------+
| 1 | 1 | completed |
+---+---+-----------+
| 2 | 2 | invalid   |
+---+---+-----------+

Thank you.

13

thanks

Heidi is great! :smiley:

could you please give me the actual counts from the two queries in post #11

14

Hi,

image
image915×405 6.5 KB

Thanks

15

okay, great, we are finally getting back to the actual problem

what is the count returned by this query

you had said earlier you got one result, but you never said what that result was

SELECT COUNT(*)
  FROM personal as p
INNER 
  JOIN personal_key as pkeyreg
    ON pkeyreg.id = p.personal_key_reg_id
 WHERE p.status IN ('valid','completed')

i’m going to predict that the count will be 2

why are you querying for the value 'valid' when you know that this doesn’t exist in the table?

16

HI,

Okay sorry I removed now the ‘valid’, I get the now count 2 result


INSERT INTO `personal_key` (`id`, `licensekey`, `licenseregisterno`) VALUES (1, 'lic-123', 'reg-223 ');
INSERT INTO `personal_key` (`id`, `licensekey`, `licenseregisterno`) VALUES (2, 'lic-124', 'reg-224');
INSERT INTO `personal_key` (`id`, `licensekey`, `licenseregisterno`) VALUES (3, 'lic-125', 'reg-225');
INSERT INTO `personal_key` (`id`, `licensekey`, `licenseregisterno`) VALUES (4, 'lic-123', 'reg-223');


INSERT INTO `personal` (`id`, `personal_key_reg_id`, `status`) VALUES (1, '1', 'completed');
INSERT INTO `personal` (`id`, `personal_key_reg_id`, `status`) VALUES (2, '2', 'completed');

image

But is it possible that I will search lic-123 and count how many times it exist in that table, and personal_key.id is exist in the personal.personal_key_reg_id ?

in my example the personal.personal_key_reg_id = 1 then I can count lic-123 ?

Thanks

17

of course it’s possible

please try it, and show both the query (text please, no screenshot) and the results it produced

18

HI,

Query 1


SELECT (SELECT COUNT(*) FROM personal_key  WHERE licensekey = "lic-123"
           OR licenseregisterno = "lic-123") AS countkeys_registerno
		   FROM personal as p
		   inner join personal_key as pkeyreg
		   ON pkeyreg.id = p.personal_key_reg_id
		   where p.status IN ('completed')
		   GROUP BY countkeys_registerno;

result count = 2

Query 2

SELECT COUNT(*)
  FROM personal as p
INNER 
  JOIN personal_key as pkeyreg
    ON pkeyreg.id = p.personal_key_reg_id
 WHERE p.status IN ('completed')		
 AND ( pkeyreg.licensekey = 'lic-123' OR pkeyreg.licenseregisterno = 'lic-123' )

result count = 1

I prefer in Query 1 because that is what I need, but still having question mark on my head if that is correct or logic error?

Thank you.

19

your Query 1 is wrong – the personal_key table in the SELECT clause is a different version of the personal_key table in the main query, even thought they’re the same table… also, the grouping is wrong

i might have been able to demonstrate this a little more clearly if your test data had been more robust

your Query 2 counts only 1 row now, so my ‘oh hai’ demonstration wouldn’t have worked

here is the bottom line – it seems you want to return a count for a specific license key, but does it matter what the status is? or do you want to count all statuses for that specific license key?

20

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.