CODEWITHSUNDEEP
sql
thefonso
thefonso

Reputation: 3390

SQL subquery with COUNT help

I have an SQL statement that works

SELECT * FROM eventsTable WHERE columnName='Business'

I want to add this as a subquery...

COUNT(Business) AS row_count

How do I do this?

Upvotes: 58

Views: 276367

Answers (4)

codingbadger
codingbadger

Reputation: 43974

This is probably the easiest way, not the prettiest though:

SELECT *,
    (SELECT Count(*) FROM eventsTable WHERE columnName = 'Business') as RowCount
    FROM eventsTable
    WHERE columnName = 'Business'

This will also work without having to use a group by

SELECT *, COUNT(*) OVER () as RowCount
    FROM eventsTables
    WHERE columnName = 'Business'

Upvotes: 85

rugg
rugg

Reputation: 551

SELECT e.*,
       cnt.colCount 
FROM eventsTable e
INNER JOIN (
           select columnName,count(columnName) as colCount
           from eventsTable e2 
          group by columnName
           ) as cnt on cnt.columnName = e.columnName
WHERE e.columnName='Business'

-- Added space

Upvotes: 22

Zephyr
Zephyr

Reputation: 7823

Assuming there is a column named business:

SELECT Business, COUNT(*) FROM eventsTable GROUP BY Business

Upvotes: 1

eumiro
eumiro

Reputation: 212835

Do you want to get the number of rows?

SELECT columnName, COUNT(*) AS row_count
FROM eventsTable
WHERE columnName = 'Business'
GROUP BY columnName

Upvotes: 10

Related Questions