help with query (count with case statement)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with query (count with case statement)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-29 : 10:58:40

Hi,

I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in
tblMessage

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")

any help much appreciated..

thanks!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 12:25:59

[code]select count(case when checked = 0 then messageFromID else NULL end) as OutboundMessages_UnChecked,count(case when checked = 1 then messageFromID else NULL end) as OutboundMessages_checked,
count(messageFromID) as OutboundMessages_total
FROM tblMessage
WHERE messageFromID = 1000[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-29 : 16:43:28

Hi visakh16,

thanks very much. I'm having a little trouble integrating it, please let me post my full query. I simplified it previously to make it easier, but realized I messed up.

How can I get the query you suggested into this statement? I keep getting syntax errors when attempting.

Thank you very much once again!!
mike123

SELECT TOP 1 *,

(select count(messageFrom) FROM tblMessage WHERE messageFrom = MES.messageFrom) as OutBoundMessages

FROM tblSpamReports SR

JOIN tblMessage MES on MES.messageID = SR.offendingID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-29 : 17:51:18

SELECT m.messageFrom,
SUM(CASE WHEN m.checked = 1 THEN 1 ELSE 0 END) AS Checked,
SUM(CASE WHEN m.checked = 0 THEN 1 ELSE 0 END) AS Unchecked,
COUNT(*) AS Total
FROM tblSpamReports AS sr
INNER JOIN tblMessage AS m ON m.messageID = sr.offendingID
GROUP BY m.messageFrom
ORDER BY m.messageFrom

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-03-30 : 07:37:44

Hi Peso,

A lesson to be learned by me. Always post the full query, I truncated it a bit, attemtping to make it easier for you guys to help me, I shouldn't have done this as it backfired

.

What you posted is exactly what I wanted, but I'm having difficulty putting it in the query below, which isn't what I originally posted.

Here below is the full query. If your able to help me out on this its much appreciated. Sorry for making this difficult

PS I have one question

Here we bring back 3 columns

SUM(CASE WHEN m.checked = 1 THEN 1 ELSE 0 END) AS Checked,
SUM(CASE WHEN m.checked = 0 THEN 1 ELSE 0 END) AS Unchecked,
COUNT(*) AS Total

I could alternatively bring back just "checked" and "unchecked" and add them together in the web app. Is getting the count(*) of any significant cost percentage wise relative to the query? It's nice to have, I'm just wondering if SQLServer already knows this value as its gone thru the table already (0,1 are the only possible values for checked on this table)

Thanks very much for any help!!!
mike123

SELECT TOP 1 spamReportID, offendingID, SR.spamTypeID, SRT.spamDescription, dateReported, reportedByUserID, UD.nameOnline as reportedByNameOnline,
MES.messageFrom, UD2.nameOnline as spamFromNameOnline, MES.message, MES.subject, MES.date, MES.checked, ISNULL(MES.IP,'') as FROM_IP, UD2.date as memberSince, dateDiff(dd, UD2.date, getDate()) as membershipDays,

(select count(messageFrom) FROM tblMessage WHERE messageFrom = MES.messageFrom) as OutBoundMail,
(select count(messageFrom) FROM tblMessage WHERE messageFrom = MES.messageFrom and message = MES.message) as OutBoundMail_Duplicates,
(select count(spamReportID) FROM tblSpamReports SR2 JOIN tblMessage MES2 ON SR2.offendingID = MES2.messageID WHERE MES2.messageFrom = MES.messageFrom) as totalSpamComplaints,

--OTHER TABLES
(select count(commentFromID) FROM tblComment WHERE commentFromID = MES.messageFrom) as OutBoundComments,
(select count(commentFromID) FROM tblPhotoComments WHERE commentFromID = MES.messageFrom) as OutBoundPhotoComments

FROM tblSpamReports SR

JOIN tblUserDetails UD ON UD.userID = SR.reportedByUserID
JOIN tblSpamReports_Types SRT ON SR.spamTypeID = SRT.spamTypeID
JOIN tblMessage MES on MES.messageID = SR.offendingID
JOIN tblUserDetails UD2 ON UD2.userID = MES.messageFrom

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-08 : 17:06:20

bump.. still a bit stuck on this one... any help much appreciated !

thanks again :D
mike123

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-04-08 : 19:22:09

select
DRIVEDTABLE.TransDate AS [Trans Date],
ISNULL(DRIVEDTABLE.DepositSum,0) AS [total deposits],
ISNULL(DRIVEDTABLE.WithdrawSum,0)AS [total withdrawls],
ISNULL(DRIVEDTABLE.DepositSum,0)-ISNULL(DRIVEDTABLE.WithdrawSum,0)AS Closingbalance FROM (
SELECT TransDate,
SUM( CASE WHEN TransName='deposit' then
Amount
end ) as DepositSum,
SUM( CASE WHEN TransName='Withdraw' then
Amount
END ) as [Closing balance]

from dbo.DepositWithdraw
group by TransDate)AS DRIVEDTABLE
you can use this an example
drived table are very nice and handy to get these kind of works to be done

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-04-09 : 05:53:16

quote:
Originally posted by QAZAFI

select
DRIVEDTABLE.TransDate AS [Trans Date],
ISNULL(DRIVEDTABLE.DepositSum,0) AS [total deposits],
ISNULL(DRIVEDTABLE.WithdrawSum,0)AS [total withdrawls],
ISNULL(DRIVEDTABLE.DepositSum,0)-ISNULL(DRIVEDTABLE.WithdrawSum,0)AS Closingbalance FROM (
SELECT TransDate,
SUM( CASE WHEN TransName='deposit' then
Amount
end ) as DepositSum,
SUM( CASE WHEN TransName='Withdraw' then
Amount
END ) as [Closing balance]

from dbo.DepositWithdraw
group by TransDate)AS DRIVEDTABLE
you can use this an example
drived table are very nice and handy to get these kind of works to be done

hmm not sure if this is posted in the wrong thread? or if I should be able to figure it out from your sample code ?

Thanks again, :)
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources