CODEWITHSUNDEEP
sqlsql-server
JasonH
JasonH

Reputation: 1241

SQL Query counting appearances in multiple columns

Here is an example of my table:

ID     Name     ClickLink1     ClickLink2     ClickLink3
--     ----     ----------     ----------     ----------
1      John     Landing                       ThankYou
2      Abby     ThankYou       Landing        Landing
3      Chris                   ThankYou
4      Sam      Landing        ThankYou       ThankYou

I'm looking for results such as:

Page       Link         Count
----       ----         -----
Landing    ClickLink1   2
Landing    ClickLink2   1
Landing    ClickLink3   1

Ultimately I will repeat the query in a separate report for the "ThankYou" page, but I can easily duplicate based off query for "Landing" page.

Using SQL Server 2008 R2

Upvotes: 0

Views: 467

Answers (5)

Jim
Jim

Reputation: 6881

I think this would do it below, and should be an agnostic solution since you didn't provide the DBMS flavor.

SELECT ClickLink1 as Page, 'ClickLink1' as Link, Count(ID) as Count
FROM myTable
GROUP BY ClickLink1
UNION
SELECT ClickLink2 as Page, 'ClickLink2' as Link, Count(ID) as Count
FROM myTable
GROUP BY ClickLink2
UNION
SELECT ClickLink3 as Page, 'ClickLink3' as Link, Count(ID) as Count
FROM myTable
GROUP BY ClickLink3

Upvotes: 1

David Weinberg
David Weinberg

Reputation: 1091

I think the solution is un pivot.

create table #a
(
    id int identity,
    Name varchar(500),
    ClickLink1 varchar(500),
    ClickLink2 varchar(500),
    ClickLink3 varchar(500),
)
insert into #a
select 'John','Landing',null,'ThankYou'
union
select 'Abby','ThankYou','Landing','Landing'
union
select 'Chris',null,'ThankYou',null
union
select 'Sam','Landing','ThankYou','ThankYou'

select Name,PAge,COUNT(*) count
from (
    select *
    from #a
)p
UNPIVOT
   (Page FOR clicklink IN 
      (ClickLink1, ClickLink2, ClickLink3)
)AS unpvt
where Page = 'Landing'
group by Name,PAge

Upvotes: 0

Smaug
Smaug

Reputation: 2673

As you didn't mentioned that your destination database, I just created a simple union all query for your required output. However, I've given the conversion tool link which helps you to convert this query on the destination databases optimistic query(Oracle,IBM DB2, MySQL, Sybase, PostgreSQL, Informix and Netezza )

[Conversion Tool Link1

select ClickLink1 as 'Page', sum(ClickLink1) 'count', 'ClickLink1'
from testtable group by ClickLink1, ClickLink1
union all 
select ClickLink2 as 'Page', sum(ClickLink2) 'count', 'ClickLink2'
from testtable group by ClickLink2,ClickLink2
union all
select ClickLink3 as 'Page', sum(ClickLink3) 'count', 'ClickLink3
from testtable group by ClickLink3

Upvotes: 0

Taryn
Taryn

Reputation: 247680

The main issue is that your current table is denormalized so you need to count across the columns. One way to do this would be to unpivot the data from multiple columns into multiple rows.

There are a few different ways that you can do this. You can use a UNION ALL to converts the columns into rows and then count the values:

select page, link, count(*) Total
from
(
  select ClickLink1 as page, 'ClickLink1' as link
  from yourtable
  union all
  select ClickLink2 as page, 'ClickLink2' as link
  from yourtable
  union all
  select ClickLink3 as page, 'ClickLink3' as link
  from yourtable
) d
where page = 'Landing'
group by page, link;

See SQL Fiddle with Demo. Another way would be to use a CROSS JOIN to a virtual table and count the values:

SELECT page, 
  col as link,
  COUNT(*) AS TOTAL
FROM 
(
  SELECT col, 
    CASE s.col
      WHEN 'ClickLink1' THEN ClickLink1
      WHEN 'ClickLink2' THEN ClickLink2
      WHEN 'ClickLink3' THEN ClickLink3
    END AS page
  FROM yourtable
  CROSS JOIN 
  (
    SELECT 'ClickLink1' AS col UNION ALL 
    SELECT 'ClickLink2' UNION ALL 
    SELECT 'ClickLink3'
  ) s
) s
where page = 'Landing'
group by page, col;

See SQL Fiddle with Demo. Depending on your database that you are using you might be able to use an UNPIVOT function along with the aggregate to get the result. For example, if you are using SQL Server you can use:

select page, link, count(*) Total
from yourtable
unpivot
(
  page
  for link in (ClickLink1, ClickLink2, ClickLink3)
) unpiv
where page = 'Landing'
group by page, link;

See SQL Fiddle with Demo

Upvotes: 2

roman
roman

Reputation: 117370

If you're using SQL Server, you can use transfer to XML trick, which I use all the time when I need to transform data to EAV, it's very general and easy to maintain and change:

declare @Data xml

select @Data = 
(
    select *
    from test
    for xml raw('Data')
)

;with cte as
(
  select
      T.C.value('.', 'nvarchar(128)') as Page,
      T.C.value('local-name(.)', 'nvarchar(128)') as Link 
  from @Data.nodes('Data/@*') as T(C)
)
select Page, Link, count(*)
from cte
where Page = 'Landing'
group by Page, Link

SQL FIDDLE EXAMPLE

Upvotes: 0

Related Questions