SQL aggregation functions

Question

Given the following table

Month        Product  Sales
---------    -------  -----    
January         1      10
January         2      15
February        3      23
March           1      15

The query I've used is:

SELECT DISTINCT a.* FROM( -- possibly needed for where clause
    SELECT dateadd(month, x.MonthOffset,0) AS mes, x.produto, Sum(x.quantidade) AS vendas
    FROM
    (
        SELECT DATEDIFF(month, 0, a.data) AS MonthOffset, a.produto, a.quantidade
        FROM (
          SELECT l.*, fc.data FROM LINHAS AS l JOIN FacturaCli AS fc
              ON fc.codigo = l.codigo 
              UNION ALL
          SELECT l.*, ff.dataEmissão FROM LINHAS AS l JOIN FacturaForn AS ff
              ON ff.codigo = l.codigo
        ) AS a
    ) AS x
    GROUP BY MonthOffset, produto
) AS a

I would like to get the product with more sales per month.

I'm having trouble doing query that requires a field that must not be in "aggregate group". Note that the table I provided is a result from a query.

I'm using SQL Server 2008. I can't use temporary variables or over clause. I wouldn't like to repeat the query that lead me to this table! But if it is the only way, it's ok.

Expected result:

Month        Product  Sales
---------    -------  -----    
January         2      15
February        3      23
March           1      15

Why can't you use an aggregate function? It seems the most sensible option in this context. — Darren
– Darren, Commented Jun 22, 2012 at 10:18
I can use an aggregate function. What I meant is that product must appear in result and the agregrate group is only month for the query i need — brunoss
– brunoss, Commented Jun 22, 2012 at 10:33

Romil Kumar Jain · Accepted Answer · 2012-06-22 10:15:19Z

1

A very tricky solution for your problem.

SELECT a.* 
FROM   mytable a 
       LEFT JOIN mytable b 
              ON a.Month = b.Month
                 AND a.Sales < b.Sales
WHERE  b.Month IS NULL

answered Jun 22, 2012 at 10:15

Romil Kumar Jain

20.8k9 gold badges67 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

brunoss Over a year ago

what if I wouldnt like to reapeat the query ive used? I can't use temporary variables (college requires that)

Romil Kumar Jain Over a year ago

@user1279792 which database and version you are using?

brunoss Over a year ago

sql server 2008 can't use over too

MatBailie Over a year ago

@user1279792 - Could you edit your question and include the following? What the source data looks like. What you want the results to look like. What you are allowed to use. What you're not allowed to use. And mark it as 'homework' if that's what it is?

brunoss Over a year ago

I just can't do anything about the looking of source data. I can however insert the query that lead me to that result

|

Collectives™ on Stack Overflow

SQL aggregation functions

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related