Count Nested Select Result And Sum up- SQL

Question

The first SELECT for example returns 57 and 61. I want the second sql command to use returned rows of first SELECT not like what I have put here (manually 57 and 61). Finally calculate sum of these results. (9 is the result of course)

1. SELECT id FROM permissions WHERE title = 'samplezip'   
2. SELECT count(id) FROM accesslogs WHERE urlid=57 AND status='d' //result 7  
3. SELECT count(id) FROM accesslogs WHERE urlid=61 AND status='d' //result 2

score 2 · Accepted Answer · 2017-12-13 10:28:40Z

2

You can do this all in one i think:

select
    count(l.id)
from
    permissions as p
    inner join accesslogs as l
        on p.id = l.urlid
where 
    p.title = 'samplezip'
    and status ='d'

edited Dec 13, 2017 at 10:28

answered Dec 13, 2017 at 10:14

user359135

Sign up to request clarification or add additional context in comments.

2 Comments

Messi Meysam Over a year ago

Oh, Now it gives two rows. not calculated sum! Before group by it was correct.

M Khalid Junaid Over a year ago

@MessiMeysam i have rolled back the changes made in gordatron's answer, just also ignore p.title, p.id from select part these values will be returned in in-deterministic order

Thorsten Kettner · Accepted Answer · 2017-12-13 10:22:01Z

1

Use IN:

SELECT count(*)
FROM accesslogs 
WHERE status = 'd'
  AND urlid IN (SELECT id FROM permissions WHERE title = 'samplezip');

For a result row per ID plus a total use GROUP BY WITH ROLLUP:

SELECT urlid, count(*)
FROM accesslogs 
WHERE status = 'd'
  AND urlid IN (SELECT id FROM permissions WHERE title = 'samplezip')
GROUP BY urlid WITH ROLLUP;

answered Dec 13, 2017 at 10:22

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Count Nested Select Result And Sum up- SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related