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:
apples oranges bananas
----------------------
36 24 77
That is, a count of the ‘Y’ result in each column.
Is that possible?
This might not be the best query for a mazillion records but:
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
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;
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
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.
Server: Msg 195, Level 15, State 10, Line 2
‘NVL’ is not a recognized function name.
Huh… What’s NVL?
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.
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.
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:
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.
Well, MS SQL has a CASE … WHEN … syntax, IIRC, you might be able to translate the DECODE statement into that format.
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.
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
Thanks. But…
SUM(CASE
when apples = ‘Y’ then 1
else 0
end) apples,
This works. Thanks for the help!
Darn, just a bit too late. That was what I immediately thought of, honest! 
I believe you!
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
Yeah, but like I said, I can’t change the structure.
Here’s the final query. It’s a thing of beauty.
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
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.
… and there is not a single mention of fruit in that entire query!
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.