Mohamed Mahyoub Mohamed Mahyoub - 2 days ago 5
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]
AS
SELECT
(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

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] 
as
    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
go
Comments