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 |
|
sjesweak
Starting Member
10 Posts |
 Posted - 2007-07-26 : 12:37:35
|
| Hello -I have a syntax issue. I have 2 columns in a table. First column is Station ID which has values that range from 1-4. My second column is weight of a substance that ranges from 0-120.I want do a sql query that returns 3 columns.Column 1 is the station IDColumn 2 is the Count of each weight that is > 116Column 3 is the Count of each weight that is < 116I have a query which can do 2 of the three as shown.SELECT COUNT(*) AS Expr1, CarbonFillStationBankAFROM CompleteTableWHERE (DTEntered BETWEEN DATEADD(hour, 7, @StartDate) AND DATEADD(hour, 15, @StartDate)) AND (CarbonWeight < 116.2)GROUP BY CarbonFillStationBankAand this returns 7 127 23 312 4What do I have to do to get a third count column to show the count of those weights > 116? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-26 : 12:49:41
|
SELECT StationID, SUM(CASE WHEN Substance <= 116 THEN 1 ELSE 0 END),SUM(CASE WHEN Substance > 116 THEN 1 ELSE 0 END)FROM TableGROUP BY StationIDORDER BY StationID
E 12°55'05.76"N 56°04'39.42" |
 |
|
|
sjesweak
Starting Member
10 Posts |
Posted - 2007-07-26 : 12:59:13
|
| If thanks were candy bars you would have a fed ex truck with a life time supply on it's way to your doorstep :) |
|
|
|
sjesweak
Starting Member
10 Posts |
Posted - 2007-07-26 : 13:08:37
|
| How would I format the syntax if I wanted to have all values between a certain date and time with a DTEntered field as the date/time column. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-26 : 15:37:21
|
| add the condition to the where clause_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
Count(*)