Mohamed Mahyoub Mohamed Mahyoub - 1 year ago 67
SQL Question

Count Table Rows in a nested select statement

I want to create a view that shows statistic of the database like this:

NumberOfProduct | NumberOfUsers | NumberOfOnlineUsers

45453 54665 5654645

Data source:

NumberOfProduct - Is Total rows of product table

NumberOfUsers - Number of rows in users table

NumberOfOnlineUsers - Is a row value in column called "number" located inside a table called SiteOverView where stat_name = 'NumberOfOnlineUsers'

SiteOverView Table:

stat_name | number

NumberOfOnlineUsers 5654645

NumberOfOfflineUsers 54665

NumberOfVisitors 45453

I want something like

Create View [dbo].[dashboard_stat]
(select count(*) from products) as NumberOfProduct,
(select count(*) from users) as NumberOfUsers,
(select number from SiteOverView WHERE stat_name='NumberOfOnlineUsers') as NumberOfOnlineUsers
FROM (NumberOfOnlineUsers) dashboard_stat

but i am struggling to write the statement in write syntactic.

Answer Source

select statements in SQL Sever don't need a from clause if you are individually specifying single values for each column being returned. Because of this your query should work fine without the from:

create view [dbo].[dashboard_stat] 
    select (select count(1) from products) as NumberOfProduct
          ,(select count(1) from users) as NumberOfUsers
          ,(select number
            from SiteOverView
            where stat_name = 'NumberOfOnlineUsers'
            ) as NumberOfOnlineUsers
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download