0

I'm working with the SQL version created by data.world which seems pretty generic (probably a version of MySql)
my data looks like this: 

ID   |SSM         | Abortion   | Climate     |
1     High          Low           Medium
2     High          High          Lo 
3     Low           High          High
4     Medium        Low           Low

I want to generate a SQL statement that outputs counts for each column, it would hopefully look like the following:

 Priority |  SSM    | Abortion | Climate |
 High        2           2         1
 Medium      1           0         1
 Low         1           1         2
Improve this question
5
  • Tag your question with the database you are using. Commented Aug 19, 2018 at 13:15
  • I couldn't find the SQL version they are using in their docs. My sense is its a version of mysql, but I wasn't sure. So I didn't tag mysql. Commented Aug 19, 2018 at 13:24
  • . . I added MySQL based on your supposition. You can try running select version() as mysql_version. If it is MySQL, you will get the MySQL version number. If Postgres, it'll be obvious. I think it will fail in most other databases. Commented Aug 19, 2018 at 13:38
  • Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). Commented Aug 19, 2018 at 14:14
  • @Strawberry, you got me thinking that I had conceptualized my problem incorrectly. I needed to convert the wide table to a long one so my presentation application could more easily take it. I've added another answer below that does that. Thanks Commented Aug 19, 2018 at 17:48

2 Answers 2

Reset to default
2

A relatively simple way that works in any database is to use union all:

select 'High' as priority,
       sum(case when SSM = 'High' then 1 else 0 end) as SSM,
       sum(case when Abortion = 'High' then 1 else 0 end) as Abortion,
       sum(case when Climate = 'High' then 1 else 0 end) as climate
from t
union all
select 'Medium' as priority,
       sum(case when SSM = 'Medium' then 1 else 0 end) as SSM,
       sum(case when Abortion = 'Medium' then 1 else 0 end) as Abortion,
       sum(case when Climate = 'Medium' then 1 else 0 end) as climate
from t
union all
select 'Low' as priority,
       sum(case when SSM = 'Low' then 1 else 0 end) as SSM,
       sum(case when Abortion = 'Low' then 1 else 0 end) as Abortion,
       sum(case when Climate = 'Low' then 1 else 0 end) as climate
from t;

In most databases, you can combine this to:

select p.priority,
       sum(case when t.SSM = p.priority then 1 else 0 end) as SSM,
       sum(case when t.Abortion = p.priority then 1 else 0 end) as Abortion,
       sum(case when t.Climate = p.priority then 1 else 0 end) as climate
from (select 'High' as priority, 1 as ord union all
      select 'Medium' as priority, 2 as ord union all
      select 'Low' as priority, 3 as ord
     ) p cross join
     t
group by p.priority, p.ord
order by p.ord;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I accepted @Gordon Linoffs answer since is the answer to the question. But Strawberry got me thinking that I had conceptualized the problem wrong. What I needed to do was convert a wide dataset into a long, and then do the counting. I got this from Using PIVOT to Flip Data from Wide to Tall

To do this for my example:

SELECT t.condition, t.prioirty, count(t.priority)
FROM(
SELECT "Abortion" as condition, df.abortion
  FROM df
  WHERE df.abortion!=""
UNION ALL
SELECT "SSM" as condition, df.SSM
  FROM df
  WHERE df.SSM!=""
UNION ALL
SELECT "Climate" as condition, df.climate
  FROM df
  WHERE df.climate!="" ) as t
GROUP BY t.condition, t.priority

Maybe there's an easier way to do this, but data.world doesn't support CROSS APPLY

Improve this answer

1 Comment

To my mind, the subquery is how your table should have looked to begin with

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.