SQL Server: query results from another query?

Question

I have a query below which return 124 rows to which I'm trying to count Name and return the value 124. Is there an easy way to query the results from this query or just add another column?

declare @Logins varchar(max)

Select 
    Name, UserName, CreationDate, TimeDataRetrieval, 
    TimeProcessing, TimeRendering, ByteCount, [RowCount], 
    path, TimeEnd, Format, 
    COUNT(*) over(partition by format) AS FormatCnt, 
    COUNT(NAME) AS HUH
From
    (SELECT      
        cat.Name, 
        SUBSTRING(ex.UserName, CHARINDEX('\', ex.UserName) + 1, 
        LEN(ex.UserName) - CHARINDEX('\', ex.UserName)) AS UserName, 
        cat.CreationDate, ex.TimeDataRetrieval, ex.TimeProcessing, 
        ex.TimeRendering, ex.ByteCount, ex.[RowCount], 
        cat.path, ex.TimeEnd, ex.Format
     FROM         
        ExecutionLog AS ex 
     INNER JOIN
        Catalog AS cat ON ex.ReportID = cat.ItemID) AS ZZ
--WHERE UserName in (@Logins)
--and
WHERE UserName = 'user1'
  AND (Path NOT LIKE '%Autodelivery%'
       AND Path NOT LIKE '%admin%'
       AND Path NOT LIKE '%qa%'
       AND path NOT LIKE '%test%')
GROUP BY 
   ZZ.UserName, ZZ.Name, ZZ.ByteCount, ZZ.CreationDate, ZZ.Format, ZZ.Path, 
   ZZ.[RowCount], ZZ.TimeDataRetrieval, ZZ.TimeEnd, ZZ.TimeProcessing, 
   ZZ.TimeRendering
ORDER BY 
   Name, TimeEnd DESC

if you want to just count distinct names and nothing else, then you can just do select distinct Name, count(Name) from table group by Name; — random21
– random21, Commented Apr 24, 2014 at 19:02
Which column in your current result set contains the value you want? Is it "Name" or "Huh"? — FumblesWithCode
– FumblesWithCode, Commented Apr 24, 2014 at 20:40

Matt H · Accepted Answer · 2014-04-24 19:19:36Z

1

In your code, you could use this format...

declare @Logins varchar(max)

SELECT COUNT(a.name)
FROM (    
    Select Name, UserName, CreationDate, TimeDataRetrieval, TimeProcessing,TimeRendering, ByteCount, [RowCount], path, TimeEnd, Format, COUNT(*) over(partition by format) AS FormatCnt, COUNT(NAME)AS HUH
    From
    (
    SELECT      cat.Name, SUBSTRING(ex.UserName, CHARINDEX('\', ex.UserName) + 1, LEN(ex.UserName) - CHARINDEX('\', ex.UserName)) AS UserName, 
                      cat.CreationDate, ex.TimeDataRetrieval, ex.TimeProcessing, ex.TimeRendering, ex.ByteCount, ex.[RowCount], cat.path, ex.TimeEnd, ex.Format
    FROM         ExecutionLog AS ex INNER JOIN
                      Catalog AS cat ON ex.ReportID = cat.ItemID
    ) AS ZZ
    --WHERE UserName in (@Logins)
    --and
    WHERE UserName = 'user1'
     and 
    (Path not like '%Autodelivery%'
        and Path not like '%admin%'
        and Path not like '%qa%'
        and path not like '%test%')
        GROUP BY ZZ.UserName, ZZ.Name, ZZ.ByteCount, ZZ.CreationDate, ZZ.Format, ZZ.Path, ZZ.[RowCount], ZZ.TimeDataRetrieval, ZZ.TimeEnd, ZZ.TimeProcessing, ZZ.TimeRendering
    --order by Name, TimeEnd desc
) a

edited Apr 24, 2014 at 19:19

answered Apr 24, 2014 at 19:02

Matt H

6484 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3570283 Over a year ago

I'm a little confused trying to format my query with your answer. Would I need to use another SELECT? Could you insert my query to how you think it should be structured?

user3570283 Over a year ago

Gave this error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Removing the ORDER BY returns nothing however.

user3570283 Over a year ago

Edit: It's returning a name, then how many times it shows up. I'm trying to just count all combined. Add a * in there some where?

user3570283 Over a year ago

It's returning a name, then how many times it shows up. I'm trying to just count all combined. Add a * in there some where? So just 1 value

Collectives™ on Stack Overflow

SQL Server: query results from another query?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related