SQL query that does two GROUP BYs?

I'm having trouble getting the SQL for a report I need to generate. I've got the (equivalent of the) following setup:

• A table articles (fields such as as name, manufacturer_id, etc).
• A table sales.
  • FK to articles called article_id
  • An integer called amount
  • A date field
  • A field called type. We can assume it is a string and it can have 3 known values - 'morning', 'evening' and 'night'

I want to generate an aggre开发者_如何学JAVAgated sales report, given a start date and end date:

 +--------------+---------------+--------------+-------------+
 | article_name | morning_sales | evening_sales| night_sales |
 +--------------+---------------+--------------+-------------+
 | article 1    |             0 |           12 |           2 |
 | article 2    |            80 |            3 |           0 |
...            ...             ...            ...           ...
 | article n    |            37 |           12 |           1 |
 +--------------+---------------+--------------+-------------+

I'd like to do it as efficiently as possible. So far I've been able to generate a query that will give me one type of sale (morning, evening or night) but I'm not able to do it for multiple types simultaneously. Is it even possible?

This is what I have so far; it'll give me the article name and morning sales of all the articles in a given period - in other words, the first two columns of the report:

SELECT articles.name as article_name,
       SUM(sales.amount) as morning_sales,
FROM "sales"
INNER JOIN articles ON articles.id = sales.articles_id
WHERE ( sales.date >= '2011-05-09'
    AND sales.end_date <= '2011-05-16'
    AND sales.type = 'morning'
)
GROUP BY sales.article_id

I guess I could do the same for evening and night, but the articles will be different; some articles might have sales in the morning but not in the evening, for example.

• If I have to do 1 request per sale type, how do I "mix and match" the different article lists I'll get?
• Is there a better way do do this (maybe with subqueries of some sort)?

Similarly, I'm able to build a query that gives me all the morning, evening and night sales, grouping by type. I guess my problem is that I need to do two GROUP BYs in order to get this report. I don't know how to do that, if it's possible at all.

I'm using PostgreSQL as my DB, but I'd like to stay as standard as possible. If it helps, the app using this is a Rails app.

Fortunately, you don't need to do multiple queries with your database format. This should work for you:

SELECT
  articles.name AS article_name
  SUM(IF(sales.type = 'morning', sales.amount, 0.0)) AS morning_sales,
  SUM(IF(sales.type = 'evening', sales.amount, 0.0)) AS evening_sales,
  SUM(IF(sales.type = 'night', sales.amount, 0.0)) AS night_sales
FROM sales
  JOIN articles ON sales.article_id = articles.id
WHERE
  sales.date >= "2011-01-01 00:00:00"
  AND sales.date < "2011-02-01 00:00:00"
GROUP BY sales.article_id

And if there are other types, you would have to add more columns there, OR simply sum up the other types by adding this to the SELECT clause:

SUM(
  IF(sales.type IS NULL OR sales.type NOT IN ('morning', 'evening', 'night'), 
    sales.amount, 0.0
  )
) AS other_sales

The above is compatible with MySQL. To use it in Postgres, I think you'd have to change the IF expressions to CASE expressions, which should look like this (untested):

SELECT
  articles.name AS article_name,
  SUM(CASE WHEN sales.type = 'morning' THEN sales.amount ELSE 0.0 END) AS morning_sales,
  SUM(CASE WHEN sales.type = 'evening' THEN sales.amount ELSE 0.0 END) AS evening_sales,
  SUM(CASE WHEN sales.type = 'night' THEN sales.amount ELSE 0.0 END) AS night_sales
FROM sales
  JOIN articles ON sales.article_id = articles.id
WHERE
  sales.date >= "2011-01-01 00:00:00"
  AND sales.date < "2011-02-01 00:00:00"
GROUP BY sales.article_id

Two options.

Option 1. A single join with computed columns for agreggation

select article_name  = a.article_name ,
       morning_sales = sum( case when sales.type = 'morning' then sales.amount end ) ,
       evening_sales = sum( case when sales.type = 'evening' then sales.amount end ) ,
       night_sales   = sum( case when sales.type = 'night'   then sales.amount end ) ,
       other_sales   = sum( case when sales.type in ( 'morning','evening','night') then null else sales.amount end ) ,
       total_sales   = sum( sales.amount )
from articles a
join sales    s on s.articles_id = a.articles_id
where sales.date between @dtFrom and @dtThru
group by a.article_name

Option 2. multiple left joins

select article_name = a.article_name ,
       morning_sales = sum( morning.amount ) ,
       evening_sales = sum( evening.amount ) ,
       night_sales   = sum( night.amount   ) ,
       other_sales   = sum( other.amount   ) ,
       total_sales   = sum( total.amount   )
from      articles a
left join sales    morning on morning.articles_id = a.articles_id
                          and morning.type        = 'morning'
                          and morning.date between @dtFrom and @dtThru
left join sales    evening on evening.articles_id = a.articles_id
                          and evening.type        = 'evening'
                          and evening.date between @dtFrom and @dtThru
left join sales    night   on night.articles_id   = a.articles_id
                          and night.type          = 'evening'
                          and night.date between @dtFrom and @dtThru
left join sales    other   on other.articles_id = a.articles_id
                          and (    other.type is null
                                OR other.type not in ('morning','evening','night')
                              )
                          and other.date between @dtFrom and @dtThru
left join sales    total   on total.articles_id = a.articles_id
                          and total.date between @dtFrom and @dtThru
group by a.article_name