Combining sql select and Count

Question

I have two tables

A and B

 A                          B
-----------------         -----------------
a_pk (int)                b_pk (int)
a_name(varchar)           a_pk (int)
                          b_name (varchar)

I could write a query

SELECT a.a_name, b.b_name
FROM  a LEFT OUTER JOIN b ON a.a_pk = b.a_pk

and this would return me a non distinct list of everything in table a and its table b joined data. Duplicates would display for column a where different b records shared a common a_pk column value.

But what I want to do is get a full list of values from table A column a_name and ADD a column that is a COUNT of the joined values of table B.

So if a_pk = 1 and a_name = test and in table b there are 5 records that have a a_pk value of 1 my result set would be

a_name        b_count
------        -------
test           5

Vikdor · Accepted Answer · 2012-09-16 10:36:21Z

11

The query should like this :

SELECT 
    a.a_name, 
    (
        SELECT Count(b.b_pk) 
        FROM b 
        Where b.a_pk = a.a_pk
    ) as b_count 
FROM  a

edited Sep 16, 2012 at 10:36

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

answered Sep 12, 2012 at 18:28

Kundan Singh Chouhan

14.4k4 gold badges30 silver badges33 bronze badges

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

Comments

Vikdor · Accepted Answer · 2012-09-12 18:27:50Z

4

SELECT a_name, COUNT(*) as 'b_count'
FROM 
    A a
    JOIN B b
    ON a.a_pk = b.a_pk
GROUP BY a_name

answered Sep 12, 2012 at 18:27

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Comments

Dave Zych · Accepted Answer · 2012-09-12 18:29:05Z

0

SELECT
    a.name,
    (
        SELECT COUNT(1)
        FROM B b
        WHERE b.a_pk = a.a_pk
    )
FROM A a

answered Sep 12, 2012 at 18:29

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

Collectives™ on Stack Overflow

Combining sql select and Count

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related