Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group By with Count using Union

Posted on 2014-02-21
1
Medium Priority
?
375 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 30

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

604 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