February 20, 2012 at 3:57 pm
Hey, I'm hoping there is a sol to this problem
Use case:
I have a table named "Sc-Fi Book'
---------------------------
Integer User_id | Integer interested | Integer interesting | Integer purchased
1 1 0 1
2 0 1 0
3 ......
Can I write a sql stmt using
"count(user_id) as Interested, count(user_id) as Interesting, count(user_id) as purchased
from ...
.....
"
i.e. for each of the types in a single sql statement.
It should look like
10 people are interested
5 people find the book interesting
4 purchased
Thank you !
February 20, 2012 at 4:04 pm
If they're all just 1 or 0 flags for each category, just use the SUM() function on each column to get the count that are interested, purchased, ect.
February 20, 2012 at 4:26 pm
Hey Linking90,
You may also want to consider a design change to your table. You should change the 3 columns other than User_ID to the bit data type if 1 and 0 are the only possible values. It will save on storage space.
February 20, 2012 at 4:45 pm
David Stokes (2/20/2012)
Hey Linking90,You may also want to consider a design change to your table. You should change the 3 columns other than User_ID to the bit data type if 1 and 0 are the only possible values. It will save on storage space.
Now working with bits can be tedious. But if storage space is a problem, or is anticipated to be a problem you can use the TINYINT data type. Save space and less handling problems with simple selects / updates etc.
tinyint Value (0 to 255) Space utilized 1 Byte
The SQL Server Database Engine optimizes storage of bit columns.
If there are 8 or less bit columns in a table, the columns are stored as 1 byte.
February 20, 2012 at 5:46 pm
This (actually, the solution posted by Rory)
DECLARE @Table TABLE
( UserID INT , Interested INT
, Interesting INT , Purchased INT )
INSERT INTO @Table (UserID ,Interested, Interesting, Purchased )
SELECT 1 ,1 ,0 ,1
UNION ALL SELECT 2 ,0 ,1 ,0
UNION ALL SELECT 3 ,1 ,0 ,0
UNION ALL SELECT 4 ,1 ,0 ,1
UNION ALL SELECT 5 ,1 ,0 ,1
UNION ALL SELECT 6 ,1 ,1 ,1
-- Rory's solution
SELECT SUM(T.Interested) [people are interested]
,SUM(T.Interesting) [people find the book interesting]
,SUM(T.Purchased) [purchased]
FROM @Table T
If the columns are not integers (either BIT or VARCHAR) , you can use this
-- ColdCoffee's solution
SELECT SUM(CASE WHEN T.Interested = 1 THEN 1 ELSE 0 END ) [people are interested]
,SUM(CASE WHEN T.Interesting = 1 THEN 1 ELSE 0 END ) [people find the book interesting]
,SUM(CASE WHEN T.Purchased = 1 THEN 1 ELSE 0 END) [purchased]
FROM @Table T
February 21, 2012 at 8:27 am
ColdCoffee (2/20/2012)
This (actually, the solution posted by Rory)
DECLARE @Table TABLE( UserID INT , Interested INT
, Interesting INT , Purchased INT )
INSERT INTO @Table (UserID ,Interested, Interesting, Purchased )
SELECT 1 ,1 ,0 ,1
UNION ALL SELECT 2 ,0 ,1 ,0
UNION ALL SELECT 3 ,1 ,0 ,0
UNION ALL SELECT 4 ,1 ,0 ,1
UNION ALL SELECT 5 ,1 ,0 ,1
UNION ALL SELECT 6 ,1 ,1 ,1
-- Rory's solution
SELECT SUM(T.Interested) [people are interested]
,SUM(T.Interesting) [people find the book interesting]
,SUM(T.Purchased) [purchased]
FROM @Table T
If the columns are not integers (either BIT or VARCHAR) , you can use this
-- ColdCoffee's solutionSELECT SUM(CASE WHEN T.Interested = 1 THEN 1 ELSE 0 END ) [people are interested]
,SUM(CASE WHEN T.Interesting = 1 THEN 1 ELSE 0 END ) [people find the book interesting]
,SUM(CASE WHEN T.Purchased = 1 THEN 1 ELSE 0 END) [purchased]
FROM @Table T
Huh, I guess I don't work with bit columns enough. I never realized you can't directly use the sum function on them. Thanks for teaching me something today ColdCoffee. 🙂
February 21, 2012 at 8:55 am
roryp 96873 (2/21/2012)
ColdCoffee (2/20/2012)
This (actually, the solution posted by Rory)
DECLARE @Table TABLE( UserID INT , Interested INT
, Interesting INT , Purchased INT )
INSERT INTO @Table (UserID ,Interested, Interesting, Purchased )
SELECT 1 ,1 ,0 ,1
UNION ALL SELECT 2 ,0 ,1 ,0
UNION ALL SELECT 3 ,1 ,0 ,0
UNION ALL SELECT 4 ,1 ,0 ,1
UNION ALL SELECT 5 ,1 ,0 ,1
UNION ALL SELECT 6 ,1 ,1 ,1
-- Rory's solution
SELECT SUM(T.Interested) [people are interested]
,SUM(T.Interesting) [people find the book interesting]
,SUM(T.Purchased) [purchased]
FROM @Table T
If the columns are not integers (either BIT or VARCHAR) , you can use this
-- ColdCoffee's solutionSELECT SUM(CASE WHEN T.Interested = 1 THEN 1 ELSE 0 END ) [people are interested]
,SUM(CASE WHEN T.Interesting = 1 THEN 1 ELSE 0 END ) [people find the book interesting]
,SUM(CASE WHEN T.Purchased = 1 THEN 1 ELSE 0 END) [purchased]
FROM @Table T
Huh, I guess I don't work with bit columns enough. I never realized you can't directly use the sum function on them. Thanks for teaching me something today ColdCoffee. 🙂
Yeah, think of bit columns as true/false or on/off. That's how I explain it to new dev's.
Jared
CE - Microsoft
February 21, 2012 at 8:59 am
Thank you all for your responses.
I tried to simplify the problem into a table but this is my actual query where I am stuck:
select numInterested = (count(u.user_id)
from topic t
join user_topic u on
t.topic_id = u.topic_id
join user_event_link uel on
u.user_id =uel.user_id
where
u.expertise_id!='1'
and uel.event_id='1'-
and t.topic_id = '95'),
numCouldHelp = (count(u.user_id)
from topic t
join user_topic u on
t.topic_id = u.topic_id
join user_event_link uel on
u.user_id =uel.user_id
where
u.expertise_id!='1'
and u.intent_id = '2'
and t.topic_id = '95'),
from ..
(not sure what to write here)
Thanks !!
February 21, 2012 at 9:33 am
LINKING90 (2/21/2012)
Thank you all for your responses.I tried to simplify the problem into a table but this is my actual query where I am stuck:
select numInterested = (count(u.user_id)
from topic t
join user_topic u on
t.topic_id = u.topic_id
join user_event_link uel on
u.user_id =uel.user_id
where
u.expertise_id!='1'
and uel.event_id='1'-
and t.topic_id = '95'),
numCouldHelp = (count(u.user_id)
from topic t
join user_topic u on
t.topic_id = u.topic_id
join user_event_link uel on
u.user_id =uel.user_id
where
u.expertise_id!='1'
and u.intent_id = '2'
and t.topic_id = '95'),
from ..
(not sure what to write here)
Thanks !!
After FROM statement you list tables and/or views and/or CTE's from which you do want to extract data.
If you could specify the DDL's for tables you have you might have lot more relevant answer. Please follow the link at the bottom of my signature, and you will find what you need to do when posting this sort of questions.
February 21, 2012 at 9:39 am
I am trying to use the count function twice on the same attribute
count(u.user_id) but with different conditions on the same tables.
Is there another way around ?
Thanks!
February 21, 2012 at 9:43 am
Why don't you look at the other posts. Your query is a mess and will cause lots of problems.
Jared
CE - Microsoft
February 21, 2012 at 9:45 am
SELECT SubjectOfOccurance
,CountOfCondition1 = SUM(CASE WHEN Condition 1 Met THEN 1 ELSE 0 END)
,CountOfCondition2 = SUM(CASE WHEN Condition 2 Met THEN 1 ELSE 0 END)
...
,CountOfConditionN = SUM(CASE WHEN ConditionN Met THEN 1 ELSE 0 END)
FROM Table
GROUP BY SubjectOfOccurance
if it's not detailed enough, follow this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Otherwise, how anyone can guess what tables you have what data you have and what exact results you do expect...
February 21, 2012 at 10:19 am
Thank you ,
select abc = count(u.user_id CASE
WHEN
u.topic_id = '95'
and uel.event_id='1'
and u.intent_id = '2'
THEN 1 ELSE 0 END),
def = count(u.user_id CASE
WHEN
u.topic_id = '95'
and uel.event_id='1'
and u.expertise_id != '2'
THEN 1 ELSE 0 END)
FROM
topic t
join user_topic u on
t.topic_id = u.topic_id
join user_event_link uel on
u.user_id =uel.user_id
;
This is the error I get
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE
WHEN
u.topic_id = '95'
and uel.event_id='1'
and u.intent_id = '2'
THEN' at line 1
February 21, 2012 at 10:21 am
LINKING90 (2/21/2012)
Thank you ,select abc = count(u.user_id CASE
WHEN
u.topic_id = '95'
and uel.event_id='1'
and u.intent_id = '2'
THEN 1 ELSE 0 END),
def = count(u.user_id CASE
WHEN
u.topic_id = '95'
and uel.event_id='1'
and u.intent_id = '2'
THEN 1 ELSE 0 END)
FROM
topic t
join user_topic u on
t.topic_id = u.topic_id
join user_event_link uel on
u.user_id =uel.user_id
;
This is the error I get
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE
WHEN
u.topic_id = '95'
and uel.event_id='1'
and u.intent_id = '2'
THEN' at line 1
Umm... Are you using SQL Server or MySQL?
Jared
CE - Microsoft
February 21, 2012 at 10:23 am
It's MySQL !!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply