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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-07 : 10:14:47
|
| Nicola writes "I'm trying to write a sql query to extract data from a single database table in a specific format. Unfortunately, I can't figure out how to get the info I need. The problem is that I have the following fields, and a single query falls down on extraction of one or other of them. If I try and use multiple queries, it gets to the point where it's going to take ages to extract the necessary info. ID (identity)Batch number (int, identifies which group the record falls into)Subject (text, relates to batch number - i.e. all records with the same batch number have the same subject)Date (date/time)MailRead (int, number of times user has taken an action)HTMLViewedFlashViewed (both bit, 1 or 0 depending on whether action has been taken)What I need is a recordset from which I can display a table showing the data grouped by batch. If I use a 'group by' clause, it breaks because I can only extract counts, sums etc. - I can't get the subject, for example. The date I need is the latest date in each batch.An added complication is the bit and int data. I need the number of each where the value is not zero. I can use SUM for the bit fields, but not on the int data. I can't use count either, because that'll count fields containing a zero (there are no null values). So - the each row in the table must contain the following: number of items in each batch, and batch number latest date in each batchsubject of that batchsum of HTMLViewedsum of FlashViewedNumber of non-zero items in MailReadAny ideas? This has been giving me a monster headache for the last couple of days! It looks easy at first glance, but it ain't... unless I'm missing something obvious. I'm using Windows NT (service pack 6), SQL Server 7 (service pack 2).Thanks!Nick" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-07 : 11:11:40
|
| SELECT BatchNumber, Max(Date) AS LatestDate, Subject, SUM(CASE HTMLViewed WHEN 1 THEN 1 ELSE 0 END) AS HTMLViewed,SUM(CASE FLASHViewed WHEN 1 THEN 1 ELSE 0 END) AS FLASHViewed,SUM(CASE MailRead WHEN 0 THEN 0 ELSE 1 END) AS MailReadFROM myTableGROUP BY BatchNumber, Subject |
 |
|
|
|
|
|
|
|
Complex sql query on single database table (SQL Server 7)