asked on
SQL Query Group By Count(*)
I am wondering if there is a way to get single value back from a query if the query is a group by query.
For instance, if I do a query like:
select count(*), types from cars
I know I will get a single value back like 1345 rows.
But, if I do a group by I will get a whole recordset back of cars. I just wnat to know how many rows would have come back. I building a Sql Reporting Services Report and need to provide a total at the bottom of the report. The count can only come by doing a query with a group by in it.
For instance, if I do a query like:
select count(*), types from cars
I know I will get a single value back like 1345 rows.
But, if I do a group by I will get a whole recordset back of cars. I just wnat to know how many rows would have come back. I building a Sql Reporting Services Report and need to provide a total at the bottom of the report. The count can only come by doing a query with a group by in it.
Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server
Last Comment
Anthony Perkins
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Im confused by what your question is then.....do you just want this?
select count(*)
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
select count(*)
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
ASKER
Well, see the real issue is the GROUP BY. The grouping will bring the row count down a bit since it will group it obviously. Basically I am trying to write a query that give a count of how many rows are in another query.
well, you could do this:
select count(*)
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
or this:
select count(*)
from
(select *
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
) a
or this:
select *
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
select @@rowcount
select count(*)
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
or this:
select count(*)
from
(select *
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
) a
or this:
select *
from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
select @@rowcount
ASKER
I actually got it working, and a lot of it had to do with your help. Your first query was good, I wasn't sure what the tmp was all about it. It working great now though.
Ultimately this worked:
select count(*) from ( Select HistoryDetails.HistoryID, HistoryDetails.PartID, History.status from HistoryDetails, history
where HistoryDetails.Type = 'QUO' AND
history.status <> 'nq' AND history.status <> 'nqb' AND history.status <> 'nqs'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
Ultimately this worked:
select count(*) from ( Select HistoryDetails.HistoryID, HistoryDetails.PartID, History.status from HistoryDetails, history
where HistoryDetails.Type = 'QUO' AND
history.status <> 'nq' AND history.status <> 'nqb' AND history.status <> 'nqs'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson
>>Your first query was good, I wasn't sure what the tmp was all about it.<<
I suspect you are confusing reb73 with chapmandew.
I suspect you are confusing reb73 with chapmandew.
My original query looks more like this. It comes back with a bunch of rows obviously.
Select HistoryDetails.HistoryID, HistoryDetails.PartID, History.status, from HistoryDetails, history where HistoryDetails.Type = 'QUO'
and HistoryDetails.Dated between '02/01/09 12:00 AM' and '02/28/09 11:59 PM'
and HistoryDetails.historyid = history.historyid
and HistoryDetails.Salesperson