56

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?

Quintin Robinson
  • 81,193
  • 14
  • 123
  • 132
thefonso
  • 3,220
  • 6
  • 37
  • 54

4 Answers4

84

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'
Kevin Brown-Silva
  • 40,873
  • 40
  • 203
  • 237
codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • 6
    Please note that `OVER` is not supported by the SQL standard, and it won't be available across all the RDBMS (For example, [MySQL doesn't support it](http://stackoverflow.com/questions/6292679/mysql-using-correct-syntax-for-the-over-clause)). – McSonk Mar 23 '17 at 18:11
21
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

Scott Arciszewski
  • 33,610
  • 16
  • 89
  • 206
rugg
  • 531
  • 3
  • 9
10

Do you want to get the number of rows?

SELECT columnName, COUNT(*) AS row_count
FROM eventsTable
WHERE columnName = 'Business'
GROUP BY columnName
eumiro
  • 207,213
  • 34
  • 299
  • 261
  • 1
    Can't have `where` clause in group by. Use `Having` – Jim Oct 07 '10 at 18:41
  • 9
    @Jim - You can use have a `where` clause with `group by`. You would need to use `having` if you wanted to filter on the result of an aggregate when using `group by`. – codingbadger Oct 07 '10 at 18:43
  • The Where clause would need to be before Group By and the Having would need to be after the Group By. – JM1 May 17 '19 at 18:56
1

Assuming there is a column named business:

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

Zephyr was a Friend of Mine
  • 7,661
  • 4
  • 37
  • 39