SELECT COUNT for individual rows
-
Hi, I'm trying to select the COUNT for individual rows within T-SQL while performing a join as follows:
select count(a.officer as officer, e.open_date as opened, e.close_date as closed, j.can_date as cancelled) from
escrow e inner join a10 a on e.escrow = a.escrow inner join e120 j on e.escrow = j.escrow group by a.officer, close_date, open_date, j.can_dateGetting the following error while atempting this query: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near ','. Is this possible in T-SQL Thanks, Nino
-
Hi, I'm trying to select the COUNT for individual rows within T-SQL while performing a join as follows:
select count(a.officer as officer, e.open_date as opened, e.close_date as closed, j.can_date as cancelled) from
escrow e inner join a10 a on e.escrow = a.escrow inner join e120 j on e.escrow = j.escrow group by a.officer, close_date, open_date, j.can_dateGetting the following error while atempting this query: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near ','. Is this possible in T-SQL Thanks, Nino
select count(*) should give you the results you want. Count only counts the number of rows in a set. Using the field really doesn't do much unless you use distinct. A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
select count(*) should give you the results you want. Count only counts the number of rows in a set. Using the field really doesn't do much unless you use distinct. A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
SELECT a.officer as officer, e.open_date as opened, e.close_date as closed, j.can_date as cancelled, count(*) as ct FROM escrow e inner join a10 a on e.escrow = a.escrow inner join e120 j on e.escrow = j.escrow group by a.officer, close_date, open_date, j.can_date GROUP BY a.officer, a.open_date, a.close_date, a.can_date A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
SELECT a.officer as officer, e.open_date as opened, e.close_date as closed, j.can_date as cancelled, count(*) as ct FROM escrow e inner join a10 a on e.escrow = a.escrow inner join e120 j on e.escrow = j.escrow group by a.officer, close_date, open_date, j.can_date GROUP BY a.officer, a.open_date, a.close_date, a.can_date A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
Thanks for your post, Actually i'm attempting to get the count for each individual row and display it through QA. Thanks, Nino
Nino_1 wrote:
Actually i'm attempting to get the count for each individual row and display it through QA
Then the count will always be 1. So I suspect there is a gulf of comprehension somewhere: What do you mean by "each individual row"?
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Nino_1 wrote:
Actually i'm attempting to get the count for each individual row and display it through QA
Then the count will always be 1. So I suspect there is a gulf of comprehension somewhere: What do you mean by "each individual row"?
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
Hi Colin, here's what i came up with:
select distinct a.officer as Officer, d.close_date as [Date], count(d.open_date)as [Open], count(d.close_date) as Closed, count(can_date)as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrow inner join e120 j on j.escrow = d.escrow where a.officer is not null group by a.officer, d.close_dateNino
-
Hi Colin, here's what i came up with:
select distinct a.officer as Officer, d.close_date as [Date], count(d.open_date)as [Open], count(d.close_date) as Closed, count(can_date)as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrow inner join e120 j on j.escrow = d.escrow where a.officer is not null group by a.officer, d.close_dateNino
That doesn't help me much. For each of the columns you have a COUNT() on it will always return 0 or 1 (counting each non-null, and since you don't have anything to group the aggregation by it there will always be just one row to be counted) Give me an example with data in your table and what you'd like the output to be - That will be helpful.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog