0

I have a problem and I will explain the situation to understand more my needs:

  • I have all my data in one table, and I have put it into #All_Data variable. See attached image to see how it looks like
  • I want to return data showed in #Final_Data table

To do that I have done:

  • I have created an #All_Lots temp table that contains all lots
  • And I want to loop on this variable to find how many commands in each lot and put it into the #Final_Data temp table with corresponding Lot
  • I also want to get the min date and put it for the 3rd column

I have tried this query but it’s not working:

enter image description here

Select 
    #ALL_Data.Lots, 
    count(Select #All_Data.Commands 
          From #ALL_Data    
          Where #ALL_Data.Lots = #ALL_Lots.Lots) 
From
    #ALL_Data

But this is not working can you help me please thanks a lot

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HappyDAD
  • 21
  • 1
  • 1
    Please post code not screen shots – Brad Mar 29 '18 at 15:02
  • i have added the query i am trying to use to have the 2 first columns of my final result isn't not apearing ? Thanks – HappyDAD Mar 29 '18 at 15:15
  • 3
    Just `SELECT Lots, count(*), min(whateverdatefield) FROM #ALL_Data GROUP BY Lots` We don't loop in SQL, we work with sets. Here we are aggregating your set using a GROUP BY clause and aggregation functions in the SELECT clause. I'm just guessing at your `I also want to get the min date and put it for the 3rd column` requirement since there is no date field present in your sample data. – JNevill Mar 29 '18 at 15:20
  • I agree with @JNevill's comment, and would add that while temporary tables _are_ a useful feature with many applications, I kind of get the impression that you resort to them too quickly. `#All_Lots` in your example seems to have no purpose, and although you don't say exactly where `#All_Data` came from, I have to wonder if that might be an unnecessary step too. – Joe Farrell Mar 29 '18 at 15:32
  • Thank you guys, it was helful for me. @Joe Farrell, you are right i use that a lot, because i am new to SQL, but i have one more question, you put many GROUP BY as you have Operations (Count(), SUM(), ..) Thanks – HappyDAD Mar 29 '18 at 17:00
  • Yeah, SQL requires a different way of thinking than procedural programming. When possible, instead of thinking of a series of steps you would take to arrive at a result like in C or whatever, ask yourself how you can structure a query to specify what you want all at once. Keep at it and you'll get it. :) Regarding `GROUP BY`, see this question: https://stackoverflow.com/questions/7434657/how-does-group-by-work – Joe Farrell Mar 29 '18 at 18:49

0 Answers0