CODEWITHSUNDEEP
sqlpostgresqlselect
user2660852
user2660852

Reputation:

How do show a result table from two 'select'? SQL

I have 2 queries:
First query: 

select TableB.name, count(TableB.name) from TableB group by TableB.name

result:

 name | count
 Jack | 2
 Marry| 1

and
Second query: 

select DISTINCT TableA.kName, TableA.Value from TableA inner join TableB 
ON
TableA.kName=TableB.name

result:

kName | Value
Jack  | 1
Marry | 3

But I need result table:

kName | Value | newColumn
Jack  |  1    | 2
Marry |  3    | 1

where the newColumn is result of first query, How can to do it? help, please.

maybe:

select DISTINCT TableA.kName, TableA.value, 
(select TableB.name, count(TableB.name) from TableB group by TableB.name) 
AS 
newColumn from TableA inner join TableB ON TableA.kName=TableB.name

but this is not work :(

Upvotes: 3

Views: 118

Answers (2)

Devansh
Devansh

Reputation: 1267

Try this query

SELECT tableB.name, tableA.value, count(tableB.name) as newColum FROM tableB
JOIN tableA ON tableB.name = tableA.kname
GROUP BY tableB.name,tableA.value

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

select kname, value, "newColumn"
from
    (
        select name, count(name) as "newColumn"
        from tb
        group by name
    ) tb
    inner join
    (
        select distinct kname, value
        from ta
    ) ta ta.kname = tb.name

Upvotes: -1

Related Questions