Creating a SQL View where columns are data from another column

Question

I am developping a simple criminal records management system using SQL Server.

I have the following tables:

Criminal (
          CriminalID,
          Gender ...)

Victim (
        VictimID,
        Gender ...)

Crime(
       CrimeID, 
       CriminalID,
       VictimID ...)

I am interested in creating a view that gives us a simple statistic about Gender of victims by gender of criminals.

Example of wanted output:

Victim \ Criminal | Male | Female

Male              |  4   | 2

Female            |   8  | 5

Grouping the Number of crimes only by gender of victims or only by gender of criminals isnt hard , But how can i perform this task ? How to Expand the Gender of criminals into 2 separate columns and perform the needed task ?

add some subquery columns

Vland
– Vland

2015-05-24 11:44:29 +00:00
Commented May 24, 2015 at 11:44 — Vland
– Vland, Commented May 24, 2015 at 11:44

dnoeth · Accepted Answer · 2015-05-24 11:46:32Z

1

You need a conditional sum:

select criminal.gender
  ,SUM(case when victim.gender = 'M' then 1 else 0 end) as "Male"
  ,SUM(case when victim.gender = 'F' then 1 else 0 end) as "Female"
from ...
group by criminal.gender

The CASE returns either 1 or 0...

answered May 24, 2015 at 11:46

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

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

2 Comments

user3179490 Over a year ago

Thank you so much sir it worked I have a little question : can we expand the same query in the case the values of the second column are multiple ? i mean in this case it was possible since the values that gender can take is known ; is there a way we do the same operation in case for example for age (where we may have wide range of possible values that age can take) ?

dnoeth Over a year ago

@user3179490: You need to know the actual values for age and then it's SUM(case when age between 18 and 25 then 1 else 0 end). You might also look at the PIVOT functionality in SQL Server.

Collectives™ on Stack Overflow

Creating a SQL View where columns are data from another column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related