MySQL v5.7 Having this table:  id   | sprId    | load    | calc -----|----------|---------|---------    1 |        1 |       1 |       7    2 |        2 |       1 |       1    3 |        2 |       1 |       4    4 |        2 |       0 |      11    5 |        3 |       1 |       2    6 |        3 |       0 |      85    7 |        4 |       1 |       1    8 |        4 |       1 |      13   load is BIT. How to split calc column sum based on the load column value, and then group by sprId.  I'm trying to get results like   sprId    | sum_calc_load0  | sum_calc_load1 ----------|-----------------|-----------------         1 |               0 |               7         2 |              11 |               5         3 |              85 |               2         4 |               0 |              14   Right now I'm doing two queries, and works, but I know for sure it can be done with subqueries, something I don't know that much.
I have a table tabName with the following rows:  id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open) 1          2015-05-10 00:00:00   1970-01-01 ..       open 2          2015-05-10 ..         1970-01-01 ..       new 3          2015-05-10            2015-05-12          resolved 4          2015-05-11 ..         1970-01-01 ..       open 5          2015-05-11 ..         1970-01-01 ..       new 6          2015-05-11            2015-05-11          resolved 7          2015-05-12 ..         1970-01-01 ..       open 8          2015-05-12 ..         1970-01-01 ..       new 9          2015-05-12            2015-05-12          resolved   NOTE:  1970-01-01 .. is default time for all the tickets which are still open.  I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3, resolved date is not 10 but it's closed on 12 May.  I framed a query like  SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1) FROM tabName WHERE DATE_FORMAT(Resolved, '%d-%m') &gt; DATE_FORMAT(Created, '%d-%m')       AND YEAR(Created) = '2015' GROUP BY createdd;   The above query gets all the tickets whose Resolved date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.  Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.  Updated: As suggested by Abhik, i have tried in sqlfiddle site.  Here is what i created:  CREATE TABLE test    (idint,createddatetime,resolveddatetime, status varchar(15), PRIMARY KEY (id));    INSERT INTO test       (id,'created','resolved',status`) VALUES       (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),    (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),    (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),    (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),    (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),    (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),    (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),    (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),    (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')    ;   `   When i run  ` select created_date, @open:= @open+open_ticket-resolved as open from(  select   date_format(created,'%d-%m') as created_date ,  sum( case when status='open' then 1 else 0 end )+  sum( case when status='new' then 1 else 0 end )+  sum( case when status='resolved' and ( date(resolved) > date(created) or  date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,  sum(case when status = 'resolved' and date(resolved) = date(created) then 1   else 0 end) as resolved  from test  where  YEAR(Created) = '2015'  group by created_date )x,  (select @open:=0)r order by created_date ; `  I got following output  created_date   open 10-05   3 11-05   5 12-05   8  Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.
Can a Dungeons and Dragons 4e character take D&D Essentials feats? In Heroes of the Fallen Lands many of the core feats have been streamlined, some paragon feats have become available to all levels.  Before that feats were tiered by level: heroic, paragon, epic.  Can a character that was made before the Heroes of the Fallen Lands publication use Heroes of the Fallen Lands feats? Clean install Windows 8.1 or Windows 8 from OEM key Do circular wheels maximize mechanical efficiency? I recently wondered whether there is a simple proof that circular wheels maximise mechanical efficiency. By this I mean:     Show that for a wheel with a given width and cross-sectional area, the   circular wheel reaches the bottom of an incline faster than a wheel   with any other shape.   This sounds like a rather elementary problem in classical mechanics but I haven't found a proof of this fact in any of the classical mechanics texts that I have read.   Note 1: I assume that the distribution of mass is uniform across the wheel so comparable wheels will have equal mass.   Note 2: Dry friction is assumed to be present. Is there a fixed point in the MD5 transformation, i.e. does there exist x such that md5(x) == x? How to input a specific Pantone color number in Illustrator? Why does the 'b' in absorb change to a 'p' in absorption? What is the Stack Exchange (Staff) process of launching a site? Intuition on the Kullback–Leibler (KL) Divergence I have learned about the intuition behind the KL Divergence as how much a model distribution function differs from the theoretical/true distribution of the data. The source I am reading goes on to say that the intuitive understanding of 'distance' between these two distributions is helpful, but should not be taken literally because for two distributions $P$ and $Q$, the KL Divergence is not symmetric in $P$ and $Q$.   I am not sure how to understand the last statement, or is this where the intuition of 'distance' breaks down?   I would appreciate a simple, but insightful example. Let's disallow moderator nominations from people who've been suspended in the past year Ending a two handed game It’s my turn. It is a two handed game of Canasta. I have three cards left in my hand. There is only one card left in the Stock pile. Can I pick up the one card and play my hand? Number of subrings of a Field I often read that one should not place swap partitions on a SSD drive, as this may harm the device. Is this true? Can you please explain the reason to me?  Because I otherwise would have thought that placing swap on an SSD is the best choice, as it's much faster than HDDs and therefore swapping RAM contents to the SSD is not as slow as it would be with the HDD... Finding the error in this proof that 1=2 I have a "proof" that has an error in it and my goal is to figure out what this error is. The proof:  If $x = y$, then  $$ \begin{eqnarray} x^2 &amp;=&amp; xy \nonumber \\ x^2 - y^2 &amp;=&amp; xy - y^2 \nonumber \\ (x + y)(x - y) &amp;=&amp; y(x-y) \nonumber \\ x + y &amp;=&amp; y \nonumber \\ 2y &amp;=&amp; y \nonumber \\ 2 &amp;=&amp; 1 \end{eqnarray} $$    My best guess is that the error starts with the line $2y = y$. If we accept that $x + y = y$ is true, then   $$ \begin{eqnarray} x + y &amp;=&amp; y \\ x &amp;=&amp; y - y \\ x &amp;=&amp; y = 0 \end{eqnarray} $$  Did I find the error? If not, am I close? Can I purge configuration files after I've removed the package? I was doing a package removal with apt-get remove but then realized I should have done a --purge along with it to remove the configuration files.  Can I remove the packages configuration files easily or do I need to reinstall the package and then remove with a --purge? class C {   T a; public:   C(T a): a(a) {;} };   Is it legal? Let $V$ be a representation with character $\chi$.  I would like to have a formula for the characters of the representations $\mathrm{Sym}^m[V]$ and $\wedge ^m[V]$ in terms of $\chi$.  Fulton and Harris presents such a formula for $m=2$, but not for general $m$.  Is there a "nice" such expression?  I found a paper by , but this doesn't seem to be what I'm looking for (it doesn't seem to give a general formula in terms of $\chi$).  In case the link does not work, here is the reference:  X. Zhou, P. Pulay. Characters for Symmetric and Antisymmetric Higher Powers of Representations: Application to  the  Number of Anharmonic Force Constants in Symmetrical Molecules. Journal of Computational Chemistry, Vol 10, Issue 7, (1989). Japan is a volcanically active country which means it has a plethora of onsen (hot springs) all around the country.  Visiting these onsen is supposed to be a popular vacation destination for the work-weary Japanese.    I've been told that, as with other things in Japan, there is a rather strict etiquette surrounding the use of the onsen.  In particular, the two things I've read about so far are   Swimming Suits are not allowed. It is very important that one bathe immediately before entering the onsen.   Are these true?  Are there any other taboos that a naive American should be aware of? How to install Ubuntu on portable external Hard Drive? OS: Windows 7. I want install Linux Ubuntu on portable external Hard Drive USB 2.0. I read a variety of methods, but I'm confused by contradictory advices and methods. Actually, installing Ubuntu on anything other than clean PC looks much more complicated than installing Windows. I need to have Ubuntu package + Python 2.7 + C compiler installed. My external hard drive is partially filled, I need to keep all files.   Will Ubuntu run entirely on external hardrive, without writing anythinhg to Windows system folders and to registry?  Can anyone post detailed guide how install Ubuntu on external hard drive? How do I apply a patch file? I have to find the limit of this sum: $$\sum_{r=1}^{\infty}\cot ^{-1}(r^2+\frac{3}{4})$$  I tried using sandwich theorem , observing:  $$\cot ^{-1}(r^3)\leq\cot ^{-1}(r^2+\frac{3}{4})\leq\cot ^{-1}(r^2)$$  Now when I was calculating the limit of left hand expression, I convert it to $\tan^{-1}$, by using:  $$\tan^{-1}\frac{1}{x} = \cot^{-1}x$$  but couldn't sum up the terms of arctan series.     How can I proceed?   Is there any better way ?