Link to home
Create AccountLog in
Avatar of tarrigo
tarrigo

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.

ASKER CERTIFIED SOLUTION
Avatar of reb73
reb73
Flag of Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of tarrigo
tarrigo

ASKER

I am a little confused by that query. I think I should write it more true to what I am doing.

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 = 'JS' AND group by HistoryDetails.HistoryID, HistoryDetails.PartID, history.status
Avatar of chapmandew
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 = 'JS'
Avatar of tarrigo

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 = 'JS'

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 = 'JS'
) 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 = 'JS'

select @@rowcount
Avatar of tarrigo

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 = 'JS' group by HistoryDetails.HistoryID, HistoryDetails.PartID, history.status) tmp
>>Your first query was good, I wasn't sure what the tmp was all about it.<<
I suspect you are confusing reb73 with chapmandew.