Software

Ask something
Discussion Locked

Union operatior with count(*)

0 helpful
1 reply

by ken_schreck

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello,

I want to add up to table with orders into one table. If I have two tables, one with Customers and CD Orders and one with Customers and Video Orders. I only want to see how many Orders a customer placed, no matter if it is a CD or Video.
The tables have the following simple Structure:

tblCD
Customer
Order

tblVideo
Customer
Order

The following SQL will give me the summary, but I will have 2 rows for each customer, where I want just one total row for each customer.

Select Distinct Customer, Count(Order) from tblCD
UNION
Select Distict Customer, Count(Order) from tblVideo
Order by Customer

If somebody has an idea how to solve that, I would really appreciate it.

Regards Ken

1 reply

by monkeybutter

I'm assuming the Customer field is a foreign key related to a separate customer table, otherwise you haven't normalised your data properly.

Assuming it is:

SELECT customer.Customer, Count(tblVideo.OrderID+tblCD.OrderID) AS TotalCount
FROM (customer INNER JOIN tblCD ON customer.CustomerID = tblCD.CustomerID) INNER JOIN tblVideo ON customer.CustomerID = tblVideo.CustomerID
GROUP BY customer.Customer;

CustomerID is the foreign key for the customer table, and OrderID is the primary key in each of the tblCD and tblVideo tables.

Union concatenates two tables, which has its uses, but not in basic numerical assessment like this.

This discussion is closed to new replies.