Multiple COUNT functions using one SELECT

Does anyone know how to get two COUNT functions working on one SELECT statement?  I need to be able to count the total number of items a location has and the total times those items have been used in one query so I can chart the results through SSRS using a bar chart.  The chart can only accept one dataset, so the counts have to exist in the same query.

I've tried putting counts on selects using inner joins, and as subqueries of the initial select statement but haven't had any luck yet.

Thanks!!
scross1276Asked:
Who is Participating?
 
ralmadaCommented:
if you're in SQL 2008, you can take advantage of the OVER clause. Check the query I've posted below (please note that I'm assuming your column names)
More info on the OVER clause here:
 http://msdn.microsoft.com/en-us/library/ms189461.aspx

select 	distinct item
	count(item) over (partition by locationcolumn) countlocation,
	count(item) over (partition by usedcolumn) countused
from yourtable

Open in new window

0
 
Rakesh JaiminiCommented:
you can use UNION or JOINS to get the required results

if you provide some sample data with required output then query can b formed
0
 
Goodangel MatopeSoftware ArchitectCommented:
It will be easier if we know what the table structures are like. Please put even a simplified structure showing the structure of the tables.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
scross1276Author Commented:
Ugh, as luck would have it, you guys are willing to help and now I have to run to a 3.5 hour meeting.  I'll post the info you're asking for when I get out of the meeting this afternoon.  I'm VERY sorry for the delay and GREATLY appreciate your quick response!!  The other information will be posted asap.
0
 
dbidbaCommented:
Based on your original description, here is a direction you could go...

select
   TotalItesm.ItemName
  ,TotalItems.TotalCount
  ,UsedItems.UsedCount
from
   (select ItemName,count(*) as TotalCount from SomeTable where <predicate for total> group by ItemName) TotalItems
   inner join (select ItemName,count(*)as UsedCount from SomeTable where <predicate for used> group by ItemName) UsedItems
      on UsedItems.ItemName = TotalItems.ItemName

Open in new window

0
 
scross1276Author Commented:
I have never heard of the OVER function before.  It worked perfectly!  Thanks so much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.