Counting the number of times a result from a query shows up (basic SQL question)

See also questions close to this topic

• Azure Api call taking long time

  I am currently working on a project in which i need to get the list of sql databases

  I used this api call

  https://docs.microsoft.com/en-us/rest/api/sql/databases/listbyserver

  It takes three parameter namely subscription id,resource group and server-name

  I used another api to call resource group

  https://docs.microsoft.com/en-us/rest/api/resources/resourcegroups/list

  And for server-name and subscription-id,I have created dictionary for manual insertion.

  My code structure looks something like:

  For loop:To loop through server name.

  Calling resources group api

  For loop:to loop through resources- group

  Calling listbyserver api

  For loop:getting the values from list by server api call

  The problem i am facing here is ,it is taking almost an hour to get the result.

  I have worked on another project with different api call (mentioned below) but its code structure and parameters are similar and call completes within 5 mintues.

  Api call: https://docs.microsoft.com/en-us/rest/api/compute/virtualmachines/list

  I want to understand the reason why it is taking so long in case of listofserver api call.

  Is there any other way i can get the expected results.

  Thanks in advance!!!
• SQL: How to join tables with 1+ millions of records

  I want to join two tables ("products" table has 1.5 millions of records) using the following query, but after 15 minutes the query was still running and my pc was overheating (it's a lenovo v330-14ikb with 8gb of RAM), so I stopped it.

  I am very new to indexes, and I tried by creating the followings:

  • CREATE INDEX customer_id_idx1 ON orders (customer_id)
  • CREATE INDEX customer_id_idx2 ON products (customer_id)
  • CREATE INDEX customer_id_revenues_idx ON orders(customer_id,revenues)
  • CREATE INDEX customer_id_costs_idx ON products(customer_id,costs)

  This is the query:

  SELECT a.customer_id, (SUM(a.revenues) / SUM(b.costs) :: FLOAT) AS roi
  FROM orders a
  JOIN products b
  ON a.customer_id = b.customer_id
  WHERE a.customer_id IN (
      SELECT customer_id FROM (SELECT 
          customer_id,
          COUNT(*) AS n_products
      FROM products
      GROUP BY 1
      ORDER BY 2 DESC
      LIMIT 5) x
  )
  GROUP BY a.customer_id  
  ORDER BY roi DESC
  

  The output should return the ratio of revenues/costs for the top 5 customers by number of products they bought.

  I am using pgadmin. Can someone explain me how to speed up and make it compile? Thank you in advance.
• MySQL Laravel how to multiply by array of values

  I have products table which has id, price, name , and I am receiving an array of products id and quantity from front-end, what I want is to calculate the sum of price*quantity.

  I know that I can use PHP foreach, but I am looking for database way

  //Laravel php way
  
  $sum = 0;
  foreach($request->items as $item)
  {
       product = Product::find($item['product_id']);
       $sum += $product->price*$item['quantity']
  }
  

  what I want is to pass the array to mysql and make mysql handle the calculation.
• SQLite function to enable inserting single row from virtual table rarray / carray

  I want to insert a single row from an rarray(?) (Rust equivalent of the carray extension, provided by rusqlite) which provides a single-column virtual table.

  insert into mytable select somehow_transpose(*) from rarray(?)
  

  How do I implement somehow_transpose?

  Or somehow_load such that I could use:

  insert into mytable values (somehow_load(rarray(?)))
  
• Can a SQLite user-defined function take a row argument?

  They are described as scalar, but I think that refers to the return type rather than the arguments.

  I'm trying to define one in rust that will provide a TEXT value derived from other columns in the row, for convenience/readability at point of use, I'd like to call it as select myfunc(mytable) from mytable rather than explicitly the columns that it derives.

  The rusqlite example simply gets an argument as f64, so it's not that clear to me how it might be possible to interpret it as a row and retrieve columnar values from within it. Nor have I been able to find examples in other languages.

  Is this possible?
• Keyerror Python Dict raise KeyError(key) from err trading app

  I am printing my dictionary and i get the output like this:

  opening_range_bars = minute_bars.loc[opening_range_mask]
  
  print(opening_range_bars)
                         open     high      low  close  volume
  time                                                             
  2021-02-16 00:00:00-05:00  51.16  51.7500  50.4850  51.04   99823
  2021-02-17 00:00:00-05:00  50.73  51.3921  49.7025  50.91  149788
  2021-02-18 00:00:00-05:00  50.45  51.1200  49.7650  50.69  133951
  2021-02-19 00:00:00-05:00  50.64  51.4600  50.6400  51.44  133578
                                CMI                                 
                               open     high     low   close  volume
  time                                                              
  2021-02-16 00:00:00-05:00  244.47  247.565  243.26  244.52  726380
  2021-02-17 00:00:00-05:00  244.23  245.810  238.85  241.81  719238
  2021-02-18 00:00:00-05:00  239.50  241.693  237.72  241.67  878032
  2021-02-19 00:00:00-05:00  243.24  248.550  242.34  246.89  895406
  

  and as i print :

  for key in opening_range_bars.keys():
          print(opening_range_bars.keys())
  

  i get :

  MultiIndex([('CMI',   'open'),
              ('CMI',   'high'),
              ('CMI',    'low'),
              ('CMI',  'close'),
              ('CMI', 'volume')],
             )
  MultiIndex([('CMI',   'open'),
              ('CMI',   'high'),
              ('CMI',    'low'),
              ('CMI',  'close'),
              ('CMI', 'volume')],
  

  but if i want to refer to 'low' column :

  opening_range_low = opening_range_bars['low'].min()
  print(opening_range_low)
  

  i get key error:

  raise KeyError(key) from err
  KeyError: 'low'
  

  thanks for help in advance.
• How to combine multiple queries into one query with MongoDB?

  Given 3 collections containing the format

  collection = {
       "Link":link,
       "foodList":foodItems
  }
  

  I am trying to retrieve 3 food lists by passing three different links to my 3 collections, such as

  redFoods = collection1.find(redLink)
  blueFoods = collection2.find(blueLink)
  greenFoods = collection3.find(greenLink)
  

  Where

  redFoods = [red1, red2, red3, ... , redN]
  
  blueFoods = [blue1, blue2, blue3, ... , blueN]
  
  greenFoods = [green1, green2, green3, ... , greenN]
  

  Then, I want to combine redFoods, blueFoods, and greenFoods as one list that alternates foods as:

  [red1, blue1, green1, red2, blue2, green2, red3, blue3, ... , redN, blueN, greenN]
  

  How can I do this in one query step via MongoDB?

  I have tried this so far, but this doesn't seem to work:

  redLink = "meat"
  blueLink = "fruit"
  greenLink = "veggies"
  shoesList = redFoods.aggregate([
              {
                  '$match': {
                      'Link': {
                          redLink
                      }
                  }
              }, {
                  '$unionWith': {
                      'coll': 'blueFoods',
                      'pipeline': [
                          {
                              '$match': {
                                  'Link': {
                                      blueLink
                                  }
                              }
                          }
                      ]
                  }
              }, {
                  '$unionWith': {
                      'coll': 'greenFoods',
                      'pipeline': [
                          {
                              '$match': {
                                  'Link': {
                                      greenLink
                                  }
                              }
                          }
                      ]
                  }
              }])
  
• can anyone explain this sequence? "while (v8 = v5, --v5, !!v8)"

  can anyone explain this sequence? "while (v8 = v5, --v5, !!v8)"

  please pay attention especially to the "!!" double exclamation marks are unfound in C or c++ syntax
• Parse error: syntax error, unexpected 'Expires' (T_STRING), expecting ')' in /home/public_html/wp-includes/functions.php on line 1081

  i m facing this error in my WordPress site Parse error: syntax error, unexpected 'Expires' (T_STRING), expecting ')' in /home/public_html/wp-includes/functions.php on line 1081

  here is the code

  if ( $frag ) {
      $uri = substr($u
     expires Expires header.
       *     @type string $Cache-Control Cache-Control header.
       * }
       */
      $headers = (array) apply_filters( 'nocache_headers', $headers );
  }
  

  how to fix this?
• How should I do this in command prompt?

  I made a file organizer code in python that uses command prompt and I got it to work by moving it into my C:/Users/Username folder and copying my downloads folder (which is what I want to sort) but when I type in

  C:\python file_organizer.py Downloads 
  

  It says there is no such file called file_organizer.py. But now when I move the program into C:\ it says it cant find Downloads. This is my first time using command prompt does anyone have any tips.
• TypeError: Cannot read property 'current' of undefined

  I just made a weather command for discord.js, it works fine but if I run p!weather 'not a city' it'll give me an error, I can't find any mistakes in my code, does anyone knows how to fix it. I am using slappy/discord.js. This is my code:

  const Discord = require("discord.js");
  const weather = require("weather-js");
  
  module.exports = class WeatherCommand extends BaseCommand {
    constructor() {
      super('weather', 'fun', []);
    }
  
    async run(client, message, args) {
      if (!message.guild) return message.channel.send(`Try again if you are in a server channel!`);
      const city = message.content.split(" ").slice(1).join(" ")
      if (!city) return message.channel.send("I need a city to check :wink:")
  
      weather.find({search: city, degreeType: 'C'}, function(err, result) {
          if (err) {
              message.channel.send("**${arg}** Isnt inside my query, please check again")
              console.log(err.stack)
              return;
          }
          let url;
          if (result[0].current.skytext === "Mostly Sunny") url = "https://openclipart.org/image/2400px/svg_to_png/3367/ivak-Decorative-Sun.png"
          else if (result[0].current.skytext === "Mostly Cloudy" || result[0].current.skytext === "Cloudy") url = "https://upload.wikimedia.org/wikipedia/commons/thumb/3/35/Weather-heavy-overcast.svg/200px-Weather-heavy-overcast.svg.png"
          else if (result[0].current.skytext === "Partly Cloudy") url = "";
    
          var embed = new Discord.MessageEmbed()
          .setTitle(`Forecast for ${result[0].location.name}`, message.author.displayAvatarURL)
          .addField("Temperature", `**${result[0].current.temperature}ºC**`, true)
          .addField("Humidity", `**${result[0].current.humidity}%**`, true)
          .addField("Wind Speed", `**${result[0].current.windspeed.replace("mph", "ms/h")}**`, true)
          .addField("Feels Like", `**${result[0].current.feelslike}ºC**`, true)
          .addField("Status", `**${result[0].current.skytext}**`, true)
          .setTimestamp()
          .setThumbnail(result[0].current.imageUrl)
          .setColor('YELLOW')
          .setFooter(`Requested by: ${message.author.tag}`)
          message.channel.send({ embed: embed })
  })};
  }
  

  thanks
• Error in Cross correlation (crosscorr command) in MATLAB

  I have a problem with a code. I have two kinds of data (lets call them a and b). These are cells, and in each cell I have double/numbers (see images attached). I would like to cross correlate , using a loop, data of a with data of b. I am using the following code:

  for i=1:numel(a)
  [c,lag]=crosscorr(a{i},b{i})
  r = [0.65, 1];
  ii = c >= r(1) & c <= r(2)
  
  % this finds the index of he rows(2) that have x in between 
  idx = find(abs(c) > r(1) & abs(c) <= r(2));
  % number of intervals with positive check
  numIdx{i} = sum(abs(c) > r(1) & abs(c) <= r(2))
  Final{i}=(numIdx{i})'
  n=Final'
  
   end
  

  but I realise that the results are wrong, as I should have 100% correlation bacause of the similarity of the numbers.