CODEWITHSUNDEEP
sqlsql-serversubquery
Adnan Hossain
Adnan Hossain

Reputation: 117

Subquery where I can count number of times a name appears from the first query

This is the current code I have:

select Name, ECU, Identifier, Value, Max(Filetime) as "Filetime"
from dbo.view_1
where ECU='EBS7' and Identifier='88' and Value='2459579' or identifier ='87' and Value='2431629'
group by Name, ECU, Identifier, Value
ORDER BY
    MAX(Name) ASC

This is the result I get: Result of query

I would like some kind of subquery or something that counts how many times a name appears. We can see that Agon appears two times and Apollo Akka appears only once.

Any tips on what I should write?

Upvotes: 3

Views: 309

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a correlated subquery for this:

Select Name, ECU, Identifier, Value,
       Max(Filetime) as "Filetime",
       (select count(*)
       from dbo.view_1 t2 
       where t2.Name = t1.Name and
             ECU ='EBS7' and 
             ((Identifier='88' and Value='2459579') or 
             (identifier ='87' and Value='2431629')) as cnt
from dbo.view_1 t1
where ECU ='EBS7' and 
      ((Identifier='88' and Value='2459579') or 
      (identifier ='87' and Value='2431629'))
group by Name, ECU, Identifier, Value
ORDER BY MAX(Name) ASC

Upvotes: 1

SebTHU
SebTHU

Reputation: 1405

I don't know about MySQL (which product are you actually using?), but in MS-SQL this

ORDER BY MAX(Name) ASC

would not even compile. You can't order by a column which isn't in the SELECT list.

If you want to get your original resultset, with the name's frequency in the set itself, and you're using MS-SQL, you could use a CTE:

WITH theset AS 
(select Name, ECU, Identifier, Value, Max(Filetime) as "Filetime"
from dbo.view_1
where ECU='EBS7' and Identifier='88' and Value='2459579' or identifier ='87' and Value='2431629'
group by Name, ECU, Identifier, Value)

SELECT set.*,CountNames.NameCount
FROM
    theset set
INNER JOIN
    (SELECT Name,Count(*) AS NameCount FROM theset GROUP BY Name) CountNames
ON set.Name=CountNames.Name

Upvotes: 1

Related Questions