Show Group By lines that do not exist

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 08 Jul 2008 5:53 pm

Good afternoon guys, my question is as follows, I have a table with the PAYMENT date field, value, and group.

I want to make a comparison between two dates of how much was paid in each.

So my select: SELECT date, was group, SUM (value) WHERE Group = 1 AND data IN (' data1 ', ' data2 ') GROUP BY date, However in Group 2 date there is no payment, will not bring result line.

How do I show a line with date, group and value = 0??

A hug and thank you all!!
raphaelias
Location: São Bernardo do Campo - SP

Poston Wed, 09 Jul 2008 9:26 am

I think one way to do this is to do a UNION ALL of your select with another that has all dates and ZERO value. After that, make the GROUP BY.

Something like this:
Code: Select all
SELECT data, grupo, SUM(valor)
FROM
  (
  SELECT data, grupo, valor
  FROM TABELA
  WHERE grupo=1
    AND data >= 'data1'
    AND data <= 'data2'

  UNION ALL

  SELECT TRUNC(SYSDATE) + ROWNUM-1, 1 grupo, 0
  FROM ALL_OBJECTS
  WHERE TRUNC(SYSDATE) + ROWNUM-1 >= 'data1'
    and TRUNC(SYSDATE) + ROWNUM-1 <= 'data2'
  )
GROUP BY data, grupo
I have not tested if it works here, but I think it's an idea. (in this case, he's getting a date range from Date1 until date2.

:-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 10 Jul 2008 2:13 pm

Thomas, This is not a good way as they will not be only 2 dates as in the example, but it will be how the user wants.

Would have to be, I think, in just a query!

Thank you and a hug!
raphaelias
Location: São Bernardo do Campo - SP

Poston Thu, 10 Jul 2008 2:31 pm

Just a detail ...
Confirm if when no content value is zero or null.

If null then I believe this resolves your problem, because these functions (sum()) ignore null ...

Code: Select all
SELECT data, grupo, SUM( nvl(valor,) )
WHERE grupo=1 AND data IN ('data1','data2')
GROUP BY data, grupo
Int
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia

Poston Thu, 10 Jul 2008 2:31 pm

Missed the zero ... lol SELECT date, group, SUM (nvl (.0 value)) WHERE Group = 1 AND data IN (' data1 ', ' data2 ') GROUP BY date, group
joaogarcia
Location: Campinas - SP

Cordialmente,
João C. Garcia

Poston Thu, 10 Jul 2008 2:54 pm

John, even with the NVL he won't bring the line because there isn't a null record payment on that date.

What I thought was to create a select that I bring as a result a column with dates, and they do a left join with the payment table, but also isn't working.

Any other suggestions?

Thanks and best regards!
raphaelias
Location: São Bernardo do Campo - SP



Return to SQL

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 2 guests