Geek asks and answers

MySQL - union or join?

I am trying to collect statistics in a single SQL query to make the convenience of automatically sorting dates in a join. This is really only one table, but I want to calculate different cases of data.

The table I see looks something like this:

ID   In          Wanted
441  2011-03-14  0
439  2011-03-14  1
442  2011-03-14  0
428  2011-03-13  1
431  2011-03-13  1
425  2011-03-11  0
423  2011-03-11  1
420  2011-03-09  1

I approach the desired result with this query:

SELECT * FROM
(
  (SELECT date(In) n, count(date(In)) cntw, null cntl FROM items i WHERE Wanted=1 group by date(In))
union all
  (SELECT date(In) n, null cntw, count(date(In)) cntl FROM items i WHERE Wanted=0 group by date(In))
) Serie
Order by n DESC

But close is not close enough: D As a result, I get the following:

n            cntw   cntl
2011-03-14   null   2
2011-03-14   1      null
2011-03-13   2      null
2011-03-11   null   1
2011-03-11   1      null
2011-03-09   1      null

I want to "mix" the results in one line, by date:

n            cntw   cntl
2011-03-14   1      2
2011-03-13   2      null
2011-03-11   1      1
2011-03-09   1      null

As you can see, there is only one row for each date. In fact, the most ideal result would be to have even missing dates:

n            cntw   cntl
2011-03-14   1      2
2011-03-13   2      null
2011-03-12   null   null
2011-03-11   1      1
2011-03-10   null   null
2011-03-09   1      null

... but I think it is impossible.

Thank!

+3
source share
3 answers
select date(In) as n,
       sum(case when wanted = 1 then 1 else 0 end) as cntw,
       sum(case when wanted = 0 then 1 else 0 end) as cntl
    from items
    group by date(In)
    order by n desc
+3
source

, ,

SELECT * FROM
  (SELECT date(In) n, count(date(In)) cntw, null cntl FROM items i WHERE Wanted=1 group by date(In)) as a
LEFT JOIN
  (SELECT date(In) n, null cntw, count(date(In)) cntl FROM items i WHERE Wanted=0 group by date(In)) as b
ON a.n = b.n
Order by n DESC

, , , ?

CREATE TABLE #tmpFoo (
    SomeDate datetime,
    Wanted bit
)

INSERT INTO #tmpFoo VALUES ('2011-03-11', 0)
INSERT INTO #tmpFoo VALUES ('2011-03-11', 1)
INSERT INTO #tmpFoo VALUES ('2011-03-12', 0)
INSERT INTO #tmpFoo VALUES ('2011-03-12', 1)
INSERT INTO #tmpFoo VALUES ('2011-03-14', 0)

SELECT  SomeDate n, 
        count(NULLIF(Wanted,0)) cntw, 
        count(NULLIF(Wanted,1)) cntl 
    FROM #tmpFoo i  
    GROUP BY SomeDate
+1

You would use LEFT JOIN, using the field n, to get the dates where you have the material ... Then you would have UNIONa request that gives you strings where there is nothing (the information you give above does not allow me to help with what the request is : D).

+1
source

More articles:


All Articles