Solved

Group By with Count using Union

Posted on 2014-02-21
1
365 Views
Last Modified: 2014-02-21
Experts,

I have several tables that each contain product ids and the amount of a sale transaction.

e.g.

productid, price

The same productid can appear in more than one table and can appear multiple times within each table.

I want to query all the tables and return a single result set that groups the transaction counts by productid.

What I've done so far

I would like to query each table to get counts by productid from each table for those products having sales transaction counts greater than say 10.

Then I want to union those queries together and get  counts by productid from all the tables.

Here is what I have so far (showing a union for two of the tables).

SELECT  a.productid, sum(Cnt) as 'Cnt'
     
  FROM
      (

      (
      select distinct productid, count(productid) as 'Cnt'
      from Table1
      where productid is not null
      group by productid
      having count(productid) > 10
      )
      
      UNION
      
      (
      select distinct productid, count(productid) as 'Cnt'
      from Table2
      where productid is not null
      group by productid
      having count(productid) > 10
      )

      ) as a

      group by  a.productid, a.Cnt
      order by  a.productid

The output I'm getting from each individual query within the union correctly groups the productid's.

However, I am getting multiple productid's in the overall query. e.g.

productid      Cnt
22      1
23      6
72      1
72      235
74      1
76      1
76      11

How would I write the query so as to properly group the results by productid?

e.g.
id count
72  236

Thanks in advance.
0
Comment
Question by:bobinorlando
1 Comment
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 39878390
instead of

 group by  a.productid, a.Cnt

just

 group by  a.productid

Then you get the sum. Remember grouping gives you one aggregate per group. If you want to get results per productid, then don't group by more fields. Adding the Cnt to the outer grouping, every single record will be a group, unless you get a same count for a product from two of the inner tables by coincidence. As a result of this too fine grouping you get no sum, but the same counts as you get from the unions alone.

Bye, Olaf.

And how I would do it:

Select uni.productid, Count(uni.productid)
from
( select productid from table1
  union ALL
  select productid from table2
  union ALL
  select productid from table3
   ...
) as uni
group by uni.productid

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

765 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