Solved

Group By with Count using Union

Posted on 2014-02-21
1
360 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now