Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Multiple COUNT functions using one SELECT

Posted on 2010-01-08
Medium Priority
Last Modified: 2013-11-05
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.

Question by:scross1276
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26210416
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
LVL 11

Expert Comment

by:Goodangel Matope
ID: 26210435
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.

Author Comment

ID: 26210468
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 41

Accepted Solution

ralmada earned 2000 total points
ID: 26210676
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:

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

Open in new window


Expert Comment

ID: 26214568
Based on your original description, here is a direction you could go...

   (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


Author Closing Comment

ID: 31674554
I have never heard of the OVER function before.  It worked perfectly!  Thanks so much!!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question