Query Help

Dinomike20 · #1 (**permalink**) 02-08-08, 00:25

Hi,

I have a query where I'm pulling item codes from the items table and then summing the quantity sold from the invoice table. What I need to do is sum the quantity sold from the invoice table, and then in the next column i need to sum it again but only for invoices in a specified date range.

The problem is when I put the date info in the where section, it applies it to both sums. I only want it to apply to the second one.

Is there a way to do this?

Thanks!

stolze · #2 (**permalink**) 02-08-08, 02:47

Code:

SELECT SUM(...)
FROM   ...
UNION ALL
SELECT SUM(...)
FROM   ...
WHERE  ...

Dinomike20 · #3 (**permalink**) 02-08-08, 02:52

Quote:

Originally Posted by stolze

Code:

SELECT SUM(...)
FROM   ...
UNION ALL
SELECT SUM(...)
FROM   ...
WHERE  ...

Thanks. I have a GROUP BY section as well, where does this go?

r937 · #4 (**permalink**) 02-08-08, 03:16

Code:

select it.itemcode
     , sum(in.quantity) as quantity_sold
     , sum(case when in.solddate >= '2007-12-01'
                 and in.solddate  < '2008-01-01'
                then in.quantity
                else 0 end) as quantity_sold_december
  from items as it
left outer
  join invoices as in
    on in.foo = it.bar
group
    by it.itemcode

Internet Services

Web Development

Digital Marketing

Consumer Tech