Disadvantage: Not as clear how it is working "magic" is usually bad. I would use this syntax. It achives the same as Josh and Chris's suggestions, but with the advantage it is ANSI complient and not tied to a particular database vendor. Worked well for me in SQL Server without changing the 'count' to a 'sum' and the same logic is portable to other 'conditional aggregates'.
Learn more. Asked 11 years, 7 months ago. Active 1 year, 10 months ago. Viewed k times. Active Oldest Votes. Tim Barrass 4, 2 2 gold badges 22 22 silver badges 46 46 bronze badges. JoshBerke JoshBerke Chris Shaffer Chris Shaffer This solution can gives a different answers than the sum when a group only contains nulls it result in 1 instead of 0.
Old post, but thanks this helped. Wait, that just looks ugly Sturgus Sturgus 4 4 silver badges 16 16 bronze badges. I think it's better than using SUM. Or something that closely resembles it, I don't know off the top of my hat.
And of course vendors will prefer to stick with their proprietary solutions. Erwin Smout Erwin Smout I had never heard of this before, so I looked it up. According to modern-sql. Why not like this?
Michal Michal 11 1 1 bronze badge. Because he needs a lot more than just the count. He's trying to get rowcount of a part of a group, and then an aggregate of the whole group, which you can't do with a WHERE. So I used this Fandango68 Fandango68 3, 2 2 gold badges 29 29 silver badges 47 47 bronze badges. Adil B 9, 10 10 gold badges 35 35 silver badges 46 46 bronze badges. Andres Sarria Andres Sarria 1.More actions. I ran into the strangest thing this morning.
Users reported that something that has been running in less than 1 minute for years is now taking 2. I found the problem but I don't understand it. This used to run in about 10 seconds but now takes 2. I'm puzzled, I made changes to the view 2 months ago and I am guessing that it triggered the issue but still, it does not make sense to me.
Why would it take over 2 hours to execute the if statement if it only takes 10 seconds to get the record count? Why would it take over 2 hours to execute the if statement if only takes 10 seconds get the record count? Can you post the view definition along with the underlying table and index definitions? What you are describing sounds odd for sure but without seeing actual objects is guesswork. Need to split a string? Jacob Wilkins. In addition to what Sean said, the execution plans for the queries would be good to have as well.
It could just be that the problematic version got compiled with a new, much more unfortunate plan recently. Good point. If this is what you 2 meant, it does not help, the execution plan for this also ends up with a Nest Loops in the 3rd step. On the subject of sharing the plans with us, you should be able to turn on Include Actual Execution plan, run the query, and then when you look at the execution plan, right click and save it as a.
Then you can include the. Select it. Warehouse, si. Style, si. Label, si. Color, si. Dimension, si. Size, sum it. It is strange how SQL generate completely different plans for statements that actually do the same thing. Remember, to SQL Server, the queries are not the same. With the IF The second question should be more easily answered than the first, but sometimes the optimizer's assumptions are very, very wide of the mark.
I know the actual plan for the bad query would be rough to acquire, if it's taking 2. The estimated plans lack information that could be helpful discrepancies between estimated and actual rows, and between estimated executions and actual executions, for example.
In this case, I suspect we would see something like those discrepancies at work. Specifically, I would guess that in the actual plan for the long-running query that the outer input for the nested loop operator joining the Sort output to the scan of InvTransactions has well more than 1 row, and that the scan is reading more than 76, rows for each execution note that in the "good" plan the estimated row count for that scan is 2.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community.
It only takes a minute to sign up. I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero.
The problem is that the 5th street, where no user lives, doesn't appear on the result. Could I do this with SQL server? Here you've got a fiddle. Inner join gives you the intersection of 2 tables. In your case, there was no entry for 5th street in your users table and that is why join did not produce any entry for that.
Outer join right or left will give the result of inner join and in addition all non-qualifying records from the left or right table depending on the type left or right of outer join.
In this case, I put Street on the left of the join and used left outer join as you wanted all streets even count is zero in your result set. Sign up to join this community. The best answers are voted up and rise to the top. How to get a group where the count is zero? Ask Question. Asked 3 years, 4 months ago. Active 6 months ago. Viewed 81k times. Here you've got a fiddle Update: If I do right joinI've got this result: street count - 1 1st street 2 2 2nd street 5 3 3rd street 2 4 4th street 1 5 5th street 1 See this fiddle.
Ronaldo 1, 1 1 gold badge 5 5 silver badges 23 23 bronze badges. Pauwelyn H. Pauwelyn 4 4 gold badges 13 13 silver badges 32 32 bronze badges.
Because right join and right outer join are same things. I did add an explanation in my answer as suggested by jpmc Active Oldest Votes. The reason your query did not work as intended: Inner join gives you the intersection of 2 tables.
Subscribe to RSS
I've just got myself a little bit stuck with some SQL. I don't think I can phrase the question brilliantly - so let me show you.
I have two tables, one called person, one called appointment. I'm trying to return the number of appointments a person has including if they have zero. However, a person who has 0 appointments isn't returned obviously as they are not in that table. The reason why this is working, is that the outer left join will return NULL for those persons that do not have an appointment.
The aggregate function count will not count NULL values and thus you'll get a zero. This just builds on the selected answer, but as the outer join is in the RIGHT direction, only one word needs to be added and less changes.
The problem with a LEFT JOIN is that if there are no appointments, it will still return one row with a null, which when aggregated by COUNT will become 1, and it will appear that the person has one appointment when actually they have none.
I think this will give the correct results:. Learn more. Ask Question. Asked 7 years, 8 months ago. Active 1 year ago.
Viewed k times. Any suggestions please? BrownE BrownE 1, 2 2 gold badges 9 9 silver badges 13 13 bronze badges. What if I want to get zero 0 as result on single table. Active Oldest Votes. LiorYehezkely: "some of the columns" doesn't matter. The count uses the join column. Hamlet Hakobyan Hamlet Hakobyan 31k 5 5 gold badges 45 45 silver badges 64 64 bronze badges.
Tunaki k 27 27 gold badges silver badges bronze badges. Francois Mazet Francois Mazet 71 1 1 silver badge 1 1 bronze badge. Rohini C. G Rohini C. G 51 1 1 silver badge 2 2 bronze badges.
I was just going to post this same solution, very simple. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Ben answers his first question on Stack Overflow.Of course, before registration has begun for an event there are no records to display.
When that is the case I would like the record count to display as '0'. Both display Error when there are no records. Is there a way to finesse this and display 0 instead of Error when the record count is 0? I think there must be more to the story. You will get Error if the report is unbound i.
Perhaps it depends on what else is in your SQL query? I suspect the former, given the behaviour of Count [some field] as I understand it. My habit is to do Count [primary or surrogate key] - does anyone know better?
You might be thinking of DCount. I am replying to this thread I am the original poster because, in spite of the fact that someone flagged it as 'answered', in fact it has not been answered, and the problem has come up again. My report is based on a simple guery: 1 table, 2 boolean conditions in the query to restrict it to selected records. This returns Error when the query returns no records.
This was extremely helpful. Thank you Dev Center. Explore Why Office? Android ASP. Ask a question. Quick access.West Ham 1-0 Chelsea: Marko Arnautovic dazzles to hand David Moyes first win as Hammers boss as Premier. Lee Selby ends nightmare year on a high note with win in surprisingly entertaining fight against Eduardo. Anthony Yarde inflicts fourth-round stoppage on Nikola Sjekloca to extend undefeated record to 14-0 Daniel Dubois devastates bloodied veteran Dorian Darch with his SIXTH second-round knock out Swansea 1-0 West Brom: Wilfried Bony fires Paul Clement's side off the foot of the Premier League as they.
Manchester United v Manchester City: Your ultimate derby stats guide Jose Mourinho questions whether Pep Guardiola should be allowed to wear yellow ribbon supporting jailed. Jose Mourinho calls Manchester City stars cheats as the United manager accuses them of diving ahead of. Gabriel Jesus compares Manchester derby to Brazil and Argentina rivalry.
Manchester United defender Phil Jones to be fit for derby clash with Manchester City. Manchester United against City can be a joy ride even if Jose Mourinho's Red Devils park the bus in Sunday's. Jose Mourinho has a big opportunity to beat Pep Guardiola.
Jose Mourinho's side look up the table at rivals but on the pitch the tables have turned on Pep Guardiola' side MOST READ IN DETAIL DM. Available for FREE on Google Play. Premier League football, Champions Cup rugby and top class horse racing - what a couple of days' sporting action we have in store. There's a Manchester derby and a Liverpool derby, while Leinster, Munster and Ulster are all in Champions Cup action.
Gold Cup hero Sizing John makes his seasonal return in the John Durkan Chase at Punchestown on Sunday. Djakadam is looking for his third win in the race, but trainer Willie Mullins has stressed that he may not be as sharp as in previous years this time around. That could favour the Jessica Harrington-trained Sizing John and he could make his seasonal bow a winning one. Even though Everton have turned a corner since the arrival of Big Sam, I can see Liverpool thrashing them on Sunday.
The Manchester derby has hardly been a goalfest the last few times these sides have met, but both teams should score on Sunday.
Manchester City have been blitzing the majority of opposition they have faced this year and should definitely find the back of the net at least once at Old Trafford. Meanwhile, United have scored seven goals in their last two Premier League games and should do so again. Sunday's transfer news and gossipPLUS: Arsenal will reportedly move for PSG youngster Goncalo GuedesJames DeGaleWhat time is Vasyl Lomachenko vs Guillermo Rigondeaux fight.I would have been quite lost without that introduction to the material.SQL CASE
This class really stretched my brain. The TA was really helpful and very responsive to questions. Overall the course was a great experience. The more courses I take at Statistics. Well done and look forward to the continued evolution of this institution. I can't recommend this class highly enough. Professor Shmueli is an excellent teacher and has written a very clear and practical book.
The professor provided good feedback to questions and introduced a wide range of fascinating supplemental information to the course for individuals who want to go deeper. The TA was also excellent with good feedback on the homeworkI really liked how the instructors coordinated the course.
They are very friendly and very knowledgeable. I found their textbook Practical Spreadsheet Modelling an excellent resourceThe modeling techniques learned in this course will greatly assist me in understanding the use of models in the banking industry.
I am very appreciative of the instructor an the TA for their unflagged availability and contribution to this class. It was a very good class and I enjoyed it and would recommend others to take it. The course was very interesting. It was the first time I've had this level of exposure to data mining. The real life examples and exercises covered during the course helped me to understand the various approaches and techniques presented.
I feel I now have a really firm foundation on which to build and have been encouraged by this course to keep learning and working towards going even deeper into stats and analysis. The course was a master piece.
I like the style and I will take more courses. I really enjoyed this course and found it sufficiently challenging, although well explained and the instructor was very approachable and explained concepts and addressed questions clearly and fairly.
Looking forward to Biostats 2. This was a highly interactive course with a first rank expert in his topic who, fortunately, was also good communicator who likes teachingThis course is well designed and the course materials are simply awesome. The instructor is very knowledgeable in the field and has tons of examples with codes from his website, which is a huge benefit. The focus on programming and practical tips, as opposed to theoretical details, is also a great benefit. In a nutshell of the 13 Statistics.
Sen has created a jewel of a class,packing an entire semester's work into 4 weeks, but doing so with such intelligence, panache, and elegance that I enjoyed every minute of the many hours I devoted to it. His deft blending of video lectures, outside reading, sample programs, and personal feedback on homework was exemplary.
It would have taken me a whole year to learn this material on my own, if I could have done so at all. I cannot praise this course highly enoughThis course will help me handle statistical analyses and related consultancy in a correct, professional and intelligent way. I came into this course knowing the basics of what Structural Equation Modeling could do. I am leaving this course with a whole new understanding of the ways it can be used to answer many different types of research questions.
From psychometrics to multi-level modeling, I now feel like I have a whole new bag of tricks in my tool-belt. While I have read books, being able to go through exercises and get feedback was critical. I do not believe I would have come to such an understanding on my own without this course. I am only beginning to use SEM.