CODEWITHSUNDEEP
sqlsqlite
Roshnal
Roshnal

Reputation: 1314

Values from 2 tables in SQLite

I need to get values from one table and the count of the rows matching each of the values. More clearly,

Some example data for table A:

+---+-------------+
|ID |    TEXT     |
+---+-------------+
| 1 | "Fruit"     |
| 2 | "Vegetable" |
+---+-------------+

And some data for table B:

+---+----------+----+-------+
|ID |   TEXT   |A_ID| FRESH |
+---+----------+----+-------+
| 1 | "Banana" | 1  |  Yes  |
| 2 | "Carrot" | 2  |  Yes  |
| 3 | "Apple"  | 1  |  No   |
+---+----------+----+-------+

As can be seen, A_ID contains a key for a row in table A that matches the specific TEXT value in each row of B.

So what I need is, I need to get the data like this:

1 | Fruit | 1
2 | Vegetable | 1

So in the above results, the first column is the ID of table A, and the second column is the TEXT of table A and the final (3rd) column is the count of rows in table B in which the column FRESH has the value of "Yes".

I have been able to get the values similar to this, but with the total number of rows as the 3rd column, and not only the "Fresh" rows.

I tried this:

 SELECT A.ID, A.TEXT, count(B.FRESH) FROM A JOIN B ON A.ID = B.A_ID;

and this:

SELECT A.ID, A.TEXT, B.FRESH FROM A JOIN B ON A.ID = B.A_ID;

But they don't get the expected result.

As I'm a kinda newbie to SQL, I'm stuck on this. So please help? I'm using SQLite3

Upvotes: 1

Views: 76

Answers (1)

Nalaka526
Nalaka526

Reputation: 11474

SELECT 
    A.ID, 
    A.TEXT, 
    (SELECT COUNT(1) 
             FROM B 
             WHERE B.A_ID=A.ID 
                 AND B.FRESH ='YES') AS FRESH
FROM A;

Upvotes: 2

Related Questions