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?
Add a comment
|
4 Answers
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'
1 Comment
McSonk
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).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
Comments
Do you want to get the number of rows?
SELECT columnName, COUNT(*) AS row_count
FROM eventsTable
WHERE columnName = 'Business'
GROUP BY columnName
3 Comments
Jim
Can't have
where clause in group by. Use Having
codingbadger
@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.
JM1
The Where clause would need to be before Group By and the Having would need to be after the Group By.
Assuming there is a column named business:
SELECT Business, COUNT(*) FROM eventsTable GROUP BY Business
