Re: Help on query for report

Lemune wrote:
Hi Lennart.
Thanks for your reply.

I have tried your suggestion, here my new query (I'm using mysql 5)
SELECT p.name,MONTHNAME(s.date) as month,sum(CASE WHEN d.quantity is
NULL THEN 0 ELSE d.quantity END ) AS sumTotal from products p left
outer join (sales s inner join sales_details d on s.id=d.sale_id) on
p.id=d.product_id WHERE S.date BETWEEN CAST('2007-01-01 00:00:00' as
Datetime) AND CAST('2007-07-30 23:59:59' as Date) group by p.id,month
Union
SELECT p.name, MONTHNAME( s.date ) AS MONTH , 0 AS sumTotal FROM
products p, sales s WHERE NOT EXISTS (SELECT p.name,
MONTHNAME( s.date ) AS MONTH , sum(CASE WHEN d.quantity IS NULL THEN 0
ELSE d.quantity END ) AS sumTotal FROM products p LEFT OUTER JOIN
( sales s INNER JOIN sales_details d ON s.id = d.sale_id ) ON p.id =
d.product_id WHERE S.date BETWEEN CAST( '2007-01-01 00:00:00' AS
Datetime ) AND CAST( '2007-07-30 23:59:59' AS Date ) GROUP BY p.id,
MONTH );

It still doesn't give me the 0 value for my question.
Where did i make mistake for my new query, because i understand that
WHERE NOT EXISTS will give me value 0 for condition where it give
nothing.


Sorry for any confusion I might have caused. You still need to generate the domain of all possible values. Either create a table and fill it with all possible values, or construct one on the fly. I dont know how to do this in mysql, but here are some examples I've seen in other dbms:

lateral(values ('January'),('February', etc ) ) month_names (x)

(select 'January' union Select 'February' union etc )) month_names (x)

If you create a permanent table, it is probably better to do it on date basis rather than on a month basis


.

Relevant Pages

  • Re: Looking for percentage of aggregate, grouped on column value
    ... > the sum of all values for a column, and #2 the sum of values for the ... Self-join, outer join, temp duplicate ... I don't know mysql, but if it supports views,then build your result set ... can access the same table more than once in a query, IOW, yes a self ...
    (comp.databases)
  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)
  • Re: Problem with SQL and Recordset
    ... This assumes that the name of the subform control on the parent form is the ... NextSuffix field from the "query" to be entered into the Suffix field on ... Dim mySQL As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... form's record source to be a query with an outer join of the ... This form is continuous (actually a subform), ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: [Q]Problems related to the MySQL linked Server.
    ... The error messge when I query data with following SQL. ... select @v_intUserNo = uno ... where userid = 'testid' ... I have two questions about the linked server for the MySQL. ...
    (microsoft.public.sqlserver.odbc)