SQL one table aggregation

For the last few days I have been attempting to find a method to pull a very important set of information from a table that contains what I call daily counts. I have a table that is setup as follows.

person|company|prod1|prod2|prod3|gen_date

Each company has more than one person, and each person can have diff开发者_Python百科erent combinations of products that they have purchased. What I have been trying to figure out is a SQL statement that will list the number of people that have bought a particular product per company. So an output similar to this:

Comp ABC | 13 Prod1 |  3 Prod2 | 5 Prod 3
Comp DEF |  2 Prod1 | 15 Prod2 | 0 Prod 3
Comp HIJ |  0 Prod1 |  0 Prod2 | 7 Prod 3 

Currently if a person did not select a product the value being stored is NULL.

Best I have right now is 3 different statements that can produce this information if run on their own.

SELECT Count(person) as puchases, company 
FROM Sales  WHERE prod1 = '1' and gendate = '3/24/2010' 
Group BY company

SELECT      company,
            SUM(COALESCE(prod1, 0)) AS total_prod1,
            SUM(COALESCE(prod2, 0)) AS total_prod2,
            SUM(COALESCE(prod3, 0)) AS total_prod3
FROM        Sales  
WHERE       gendate = '2010-03-24' 
GROUP BY    company

But you definitely should normalize you table - split it in 4:

• Company,
• Person,
• Product,
• Person_Product_Purchase (with the date of the purchase).

If you just want to check whether the value is in any of the product fields then that is simply done with an OR operator:

SELECT company, COUNT(person) as purchases
FROM Sales
WHERE (prod1 = '1' OR prod2 = '1' OR prod3 = '1')
AND gendate = '3/24/2010'
GROUP BY company

This won't perform very well, however, and you'll have a hard time getting it to perform well, because your schema hasn't been normalized properly. If you can, you should fix it to something like this:

Person (PersonID, CompanyID)
Sales (PurchaseID, PersonID, ProductID, GenDate)

Then this query (and many other queries) will be a lot easier to write:

SELECT p.CompanyID, COUNT(*) AS purchases
FROM Person p
INNER JOIN Sales s
    ON s.PersonID = p.PersonID
WHERE s.ProductID = 1
AND s.GenDate = '20100324'
GROUP BY p.CompanyID