Databases
StackOverflow2013
Postsdbo
POt-sql return number of unique values in row
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POt-sql return number of unique values in row
    text
    copied!<p>I have tried now for a couple of hours to return, a fourth row which would tell me the number of disctinct doman_namn. I keep trying to add a COUNT(DISTINCT doman_namn) but then i need a group by, which in turn destroys the purpose.</p> <p>This is stored proc which takes two parameters:</p> <pre><code>@keyword[varchar](100), @domannamn [varchar](100) </code></pre> <p>It uses two tables which looks like this</p> <p>FIRST TABLE table t_doman columns; doman_id doman_name</p> <p>SECOND TABLE table t_ranking columns; ranking_position ranking_date ranking_keyword ranking_id_doman</p> <p>I use the @keyword to look up the correct rows in t_ranking table, there then i join on ranking_id_doman on doman_id to get the "doman_name", There will be a set of name's for every date, which in turn makes the names reapeat themselves for every date, i need to check how many distinct names there are and return that in a row.</p> <p>I need it to work like:</p> <pre><code>Ranking position, date, name, number of distinct name's 1___________________2012-11-11, tony, 3 2___________________2012-11-11, chris, 3 3___________________2012-11-11, peter, 3 1___________________2012-11-10, tony, 3 2___________________2012-11-10, chris, 3 3___________________2012-11-10, peter, 3 </code></pre> <hr> <pre><code> SELECT ranking_position, CONVERT(varchar(10),ranking_date, 120), doman_namn --Here's my my attempt COUNT(DISTINCT doman_namn) as 'number_of_discint_names' FROM (SELECT ranking_position, ranking_date, ranking_id_doman FROM dbo.t_ranking WHERE ranking_keyword = 'keyword' AND ranking_date BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE() AND ranking_id_doman IN (SELECT doman_id FROM dbo.t_doman WHERE doman_namn LIKE 'doman' + '%')) as tr JOIN dbo.t_doman td on tr.ranking_id_doman = td.doman_id --GROUP BY doman_namn ALSO IT DOES NOT WORK ORDER BY ranking_date ASC </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload