Finding entry page, exit page and bounces -sql

My table structure is as follows: Sessionid Pageurl timestamp abc1 /testpage1 1465374987308 abc1 /testpage2 1465375020477 abc2 /testpage2 1465374987308 I wish to create a report of entry page count, exit page count and bounces count per page. For any session, the first page is entry page and last page an exit page. A bounce occurs when […]

Join certain rows to the results

I’m trying to make a query that will call get all that matches col3 = 1 and get the rest of the group col4 = 123 while having col2 be the distinct value. My table looks like ID col1 col2 col3 col4 ——————————— 1 A1 A NULL 123 2 B1 B NULL 123 3 C1 […]

Conditional probability p(y|x) in SQL

How to calculate conditional probability in vendor agnostic SQL code while reading a precomputed table (histogram) just once? Let’s imagine we have a query which returns a histogram relation. The histogram contains following attributes: {x, y, cnt}, where cnt is the count of occurrences of nominal attributes x and y. And calculation of the histogram […]

SQL count occurrences rows

I have a table like this: c1|c2 a| 2 b| 1 a| 1 b| 3 a| 2 how count this to get result like this col1| col2| number of occurrences a| 1| 1 a| 2| 2 b| 3| 1 And after get max value like this: col1col2 a|2 b|1

Nested While loops in SQL?

I want to change my SQL code which generates n table using two nested While loops, like: DECLARE @count1 INT SET @count1 = 2012 DECLARE @count2 INT SET @count2 = 1 WHILE @count1 <= 2016 BEGIN WHILE @count2 <= 12 create table LGDfigRecov as select … from … WHERE FD0.mo_id=count2 AND FD0.an_id= count1 … SET […]

MS Sql Server: Trailing spaces and disk space in a varchar column in a table

Does removing trailing spaces in a Varchar column values save any disk space?

Can “constant” lookups be done efficiently within a single query?

Pop quiz, SQL Server hotshots: How many times will the following student subquery be executed? (assuming there are at least ten rows in something): SELECT TOP 10 a, b , (SELECT type_id FROM type WHERE type_code = ‘student’) student FROM something If you said 1, then like me, you assume SQL Server would recognize the […]

Return only unique values

I’m trying to get counts of how many jobs were done, regardless of personnel working on them. What I need to do is filter out any duplicates, the catch is that the entire row isn’t duplicate so DISTINCT won’t work here. I want to filter out if there is any duplication based on JobCode, JobType […]

SQL Server: Create rows of a dynamic range using a select statement

Using Ruby you can produce an array with the following code: some_range = (1..10).to_a # Returns => [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] I’m looking for something similar in SQL Server using a SELECT statement. i.e. I would like to produce this table: id 1 2 3 4 5 6 7 […]

How to add sequence number for groups (new number if same group occurs again) in an SQL query

I have a question quite similar to How to add sequence number for groups in a SQL query without temp tables To take the sample from there, I have a data-structure that consists of the first two columns of this table, and I want to generate the third column in my query: Record Group GroupSequence […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.