?
Solved

Group By with Count using Union

Posted on 2014-02-21
1
Medium Priority
?
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 2000 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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