# Is it possible to write this SQL query?
**URL:** https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819
**Category:** Factual Questions
**Created:** [March 18, 2008, 7:43pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819 "2008-03-18T19:43:05Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2008, 7:43pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/1 "2008-03-18T19:43:05Z")
create table MyFruit (
apples char(1) null,
oranges char(1) null,
bananas char(1) null)
Each field will contain the values ‘Y’, ‘N’, or NULL. What I want to get is this result:
```auto
apples oranges bananas
----------------------
36 24 77
```
That is, a count of the ‘Y’ result in each column.
Is that possible?
---
### Author:  [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)
#### Post date: [March 18, 2008, 7:54pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/2 "2008-03-18T19:54:56Z")
This might not be the best query for a mazillion records but:
```auto
SELECT
(SELECT Count(*) FROM table WHERE Apples = 'Y') AS Apples,
(SELECT Count(*) FROM table WHERE Oranges = 'Y') AS Oranges,
(SELECT Count(*) FROM table WHERE Bananas = 'Y') AS Bananas
```
---
### Author:  [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)
#### Post date: [March 18, 2008, 7:58pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/3 "2008-03-18T19:58:54Z")
Which flavor of SQL? In Oracle, I think this will work:
select sum(decode(apples, ‘Y’, 1, 0)) as apples, sum(decode(oranges, ‘Y’, 1, 0)) as oranges, sum(decode(bananas, ‘Y’, 1, 0)) as bananas from MyFruit;
---
### Author:  [@wolfman](https://boards.straightdope.com/u/wolfman)
#### Post date: [March 18, 2008, 8:04pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/4 "2008-03-18T20:04:50Z")
Here is a really bizzare way to do it, 😉 (assuming I didn’t screw up the typing).
Select ((SUM(ASCII(NVL(Apples,N)-78))/11)as Apples,((SUM(ASCII(NVL(Oranges,N)-78))/11) AS Oranges ,((SUM(ASCII(NVL(Bananas,N)-78))/11) AS Bananas
FROM MyFruit
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2008, 8:05pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/5 "2008-03-18T20:05:25Z")
This is Microsoft SQL, so no decode function.
**ZipperJJ** , I’m trying to avoid your solution, as the actual query is more complicated. That would be a huge nightmare, what with the 9 (or possible 60) columns I have. But I might just have to bite the bullet.
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2008, 8:07pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/6 "2008-03-18T20:07:24Z")
[QUOTE=wolfman]
Here is a really bizzare way to do it, 😉 (assuming I didn’t screw up the typing).
Select ((SUM(ASCII(NVL(Apples,N)-78))/11)as Apples,((SUM(ASCII(NVL(Oranges,N)-78))/11) AS Oranges ,((SUM(ASCII(NVL(Bananas,N)-78))/11) AS Bananas
FROM MyFruit
[/QUOTE]
Server: Msg 195, Level 15, State 10, Line 2
‘NVL’ is not a recognized function name.
Huh… What’s NVL?
---
### Author:  [@wolfman](https://boards.straightdope.com/u/wolfman)
#### Post date: [March 18, 2008, 8:07pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/7 "2008-03-18T20:07:49Z")
For SQL Server you will need ISNULL, not NVL, I think it does ASCII function though.
It looks through the value in the first parameter, and if it’s a null it returns the second value, rather than the null.
---
### Author:  [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [March 18, 2008, 8:14pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/8 "2008-03-18T20:14:13Z")
How attached are you to your current table structure? There’s another one that’s much less painful to work with as the number of types of fruit grows. I’m assuming that you have some kind of objects here, and for each one you want to know which types of fruit it has in it. This is pseudocode, so you’ll have to translate it into your flavor of SQL.
```auto
CREATE TABLE objects
(
objectID integer,
....,
primary key (objectID)
)
CREATE TABLE fruits
(
fruitID integer,
fruitName varchar(255),
....,
primary key (fruitID)
)
CREATE TABLE association
(
objectID integer,
fruitID integer,
primary key (objectID, fruitID),
foreign key (objectID) REFERENCES objects,
foreign key (fruitID) REFERENCES fruits
)
```
Then to get the query you want, you’ll write something like this:
```auto
SELECT fruitName, count(objectID)
FROM association NATURAL JOIN fruits
GROUP BY fruitName
```
The primary advantage this has over the other answers presented is that you don’t have to write a new query if someone adds pineapples to the database.
If you really don’t have the option to change the table, you’re stuck with what’s been presented already. It’s a bad table design, and really not the sort of thing that SQL is meant to work with.
---
### Author:  [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)
#### Post date: [March 18, 2008, 8:17pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/9 "2008-03-18T20:17:39Z")
Well, MS SQL has a CASE … WHEN … syntax, IIRC, you might be able to translate the DECODE statement into that format.
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2008, 8:26pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/10 "2008-03-18T20:26:36Z")
Unfortunately, I’m very attached to the table structure. Yeah, it’s wonky design based on the fact that char(1) takes up less room than int.
I’ve considered using case statements. **wolfman** ’s solution, while very clever, is not working for me, and I have to get this out pretty fast.
---
### Author:  [@wolfman](https://boards.straightdope.com/u/wolfman)
#### Post date: [March 18, 2008, 8:30pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/11 "2008-03-18T20:30:21Z")
I did put it wrong, the parans wern’t right.
Select ((SUM(ASCII(ISNULL(Apples,N))-78)/11)as Apples,
((SUM(ASCII(ISNULL(Oranges,N))-78)/11) AS Oranges ,
((SUM(ASCII(ISNULL(Bananas,N))-78)/11) AS Bananas
FROM MyFruit
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2008, 8:31pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/12 "2008-03-18T20:31:57Z")
[QUOTE=wolfman]
I did put it wrong, the parans wern’t right.
Select ((SUM(ASCII(ISNULL(Apples,N))-78)/11)as Apples,
((SUM(ASCII(ISNULL(Oranges,N))-78)/11) AS Oranges ,
((SUM(ASCII(ISNULL(Bananas,N))-78)/11) AS Bananas
FROM MyFruit
[/QUOTE]
Thanks. But…
SUM(CASE
when apples = ‘Y’ then 1
else 0
end) apples,
This works. Thanks for the help!
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [March 18, 2008, 8:35pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/13 "2008-03-18T20:35:22Z")
[QUOTE=tdn]
Thanks. But…
SUM(CASE
when apples = ‘Y’ then 1
else 0
end) apples,
This works. Thanks for the help!
[/QUOTE]
Darn, just a bit too late. That was what I immediately thought of, honest! 😃
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2008, 8:37pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/14 "2008-03-18T20:37:01Z")
I believe you!
---
### Author:  [@bump](https://boards.straightdope.com/u/bump)
#### Post date: [March 18, 2008, 8:40pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/15 "2008-03-18T20:40:09Z")
Why make it Y/N or NULL? Seems like a 1/0 or NULL would do the trick with something like:
select
sum(_) as Apples
,sum(_) as Oranges
,sum(\*) as Bananas
from
MyFruit
or, if you must have y/n, then the case statement you had (more or less listed below) should work jsut fine:
select
sum(case when apples = ‘y’ then 1 else 0 end)) as Apples
,sum(case when oranges = ‘y’ then 1 else 0 end)) as Oranges
,sum(case when bananas = ‘y’ then 1 else 0 end)) as Bananas
from
MyFruit
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2008, 8:50pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/16 "2008-03-18T20:50:28Z")
Yeah, but like I said, I can’t change the structure.
Here’s the final query. It’s a thing of beauty.
```auto
select
CASE HospWhere
WHEN 1 THEN 'BWH'
WHEN 2 THEN 'FH'
END Hospital,
CASE b.CPT
WHEN 2 THEN 'Lap roux'
WHEN 3 THEN 'Lap band'
END [Procedure],
SUM(CASE
when Diabetes = 'Y' then 1
else 0
end) Diabetes,
SUM(CASE
when Hypertension = 'Y' then 1
else 0
end) Hypertension,
SUM(CASE
when HyperChol = 'Y' then 1
else 0
end) HyperChol,
SUM(CASE
when LowBack = 'Y' then 1
else 0
end) LowBack,
SUM(CASE
when Arthritis = 'Y' then 1
else 0
end) Arthritis,
SUM(CASE
when Asthma = 'Y' then 1
else 0
end) Asthma,
SUM(CASE
when Reflux = 'Y' then 1
else 0
end) Reflux,
SUM(CASE
when Apnea = 'Y' then 1
else 0
end) Apnea,
SUM(CASE
when Incont = 'Y' then 1
else 0
end) Incont
from
smartsleep_bariatric ss
inner join gensurgstudies s
on ss.WMRN = s.WMRN
inner join gensurgncop n
on s.LCN = n.LCN
inner join gensurgncop_bari b
on s.LCN = b.LCN
where b.CPT in (2, 3)
and HospWhere in (1,2)
and dtPat_In_Or between '4/1/2004' and '11/1/2007'
group by HospWhere, b.CPT
```
---
### Author:  [@DMC](https://boards.straightdope.com/u/DMC)
#### Post date: [March 19, 2008, 1:23am UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/17 "2008-03-19T01:23:40Z")
A minor nitpick, if you don’t mind. I’d highly recommend you qualify your column names, and since you already gave each table an alias, it should be a piece of cake. Aside from resolving any potential future ambiguity issues, it also makes it much easier for others to follow, and is absolutely necessary if you wish for those without access to your table structures to have an understanding of your code.
Also, just in case this is an issue with your data, any records with a date of ‘11/01/2007’ that have a time of exactly midnight WILL be included in your results, but everything with a time of 00:00:00.003 and greater will not be included. Unless all of your dates have a time of 00:00:00.000 AND you intended to include data for November 1st, you might not be returning what you’re expecting to see.
I also have an idea for the code, but without the qualified columns, I can’t do much, short of guessing.
---
### Author:  [@Manduck](https://boards.straightdope.com/u/Manduck)
#### Post date: [March 19, 2008, 1:54am UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/18 "2008-03-19T01:54:31Z")
… and there is not a single mention of fruit in that entire query!
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 19, 2008, 1:04pm UTC](https://boards.straightdope.com/t/is-it-possible-to-write-this-sql-query/441819/19 "2008-03-19T13:04:26Z")
Thanks for your concerns, **DMC**. The researcher who needs this data understands it just fine. And the dates are not a problem – we’re in the habit of stripping out times, so everything happens at midnight around here.
An no, no fruit. But the patients we are reporting on should be eating more of it.