Menu
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?
361
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows. The above syntax is the general SQL 2003 ANSI standard syntax.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
It's slow because the database is iterating over all the logs and all the dashboards, then joining them, then sorting them, all before getting down to real work of grouping and aggregating.
Advantage of Using CTESince CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion. When you write a query, it is easier to break down a complex query into smaller pieces using CTE.
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' 
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
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
