google sheets – Count number of rows with a specific MONTH in the date of a specific cell and a boolean condition in another cell

Let’s say I have a number of dates and whether they occupy full or half a day…

   DATE  |         WHAT        | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | false
2021-4-5 | Something Something | false
2021-4-19| Something Something | true
2021-5-13| Something Something | false
2021-5-24| Something Something | false
2021-9-13| Something Something | true
...

I now want for each month the amount of half-days occupied in that list.
(e.g. for April that should be 1 in the examples above, for May that should be 0)

I.e. in pseudocode, I’m looking for something like

=COUNTIF(A8:A, `MOTH of row = MONTH(E2)` AND `half of row = true`)

where E2, in this case, is e.g. January stored as a date

and A8:A contains the dates of the above table (i.e. B8:B contains the WHAT and C8:C contains the HALF?)

delivery – Basket Item Count From Certain Departmartments at Checkout

The question is to do with adding code within Owebia Advanced Shipping Module, but I am hoping it may just be a simple syntax error I am not currently spotting.

Before the list of delivery methods, I am hoping to group department id’s together, and check how many products within this group are in the basket, and work the price out from there.

Here’s what I have written, appreciate help to get this working!

$group1 = function ($allItems) {
return array_sum(
    return array_map(
        function ($item) {
            return count(array_intersect($item->product->category_ids, (2, 3)))
            ? $item->qty 
            : 0;
        },
        $request->all_items
    )
),

};

sqlite – How do I show zero when I use count by using self left join?

I am trying to use count() to count the number of the module where the particular student got the average mark below 40. It also needs to show the number 0 if the student does not have any assessment mark lower than 40. However, my query does not show any 0 number at all.

CREATE TABLE assessment (
assessmentId text, 
mark integer, 
studentId text, 
moduleId text, 
PRIMARY KEY (assessmentId, studentId, moduleId), 
FOREIGN KEY(studentId) REFERENCES student(studentId), 
FOREIGN KEY(moduleId) REFERENCES module(moduleId)) ";

in each record, it shows the module that the assessment is from, and show which student took the assessment.

this is my query

 "select count(m.moduleId), avg(e.mark), m.studentId 
from assessment e 
left join assessment m on m.assessmentId = e.assessmentId 
and m.studentId = e.studentId 
and m.moduleId = e.moduleId 
group by m.studentId 
having avg(e.mark)<40";

the module could have more than one assessment, therefore the avg(e.mark) is to find out the average mark for each module for each student. Therefore it is group by studentId

so what i want the query to show is:


studentId|avg(e.mark)|count(m.moduleId)
1        | 50        | 0
2        | 20        | 2

However I am getting the result below


studentId|avg(e.mark)|count(m.moduleId)
1        | 20        | 2


I really don’t know where the problem is

c – Count edit distance

I have implemented a C code in order to answer an interview question. I would be appreciated if someone would improve the code. Task is an easy, however I want to achieve best efficiency in terms of readability and simplicity. Pardon my English again.

  • Sorry that I have just printed out the result.

The edit distance between two strings refers to the minimum number of
character insertions, deletions, and substitutions required to change
one string to the other. For example, the edit distance between
“kitten” and “sitting” is three: substitute the “k” for “s”,
substitute the “e” for “i”, and append a “g”.

Given two strings, compute the edit distance between them.

CODE:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

unsigned int count_edit_dist(const char list1(),
    const char list2())
{
    unsigned int count=0;
    size_t len_str1 = strlen(list1);
    size_t len_str2 = strlen(list2);


    for(int i=0; list1(i)!=''; i++)
    {
        if(list2(i)=='') //means len(list2) < len(list1)
        {
            count += (unsigned int)(len_str1 - len_str2);
            return count;
        }

        if(list1(i) != list2(i))
            count++;
    }

    //reacing here means len(list1) <= len(list2)
    count += (unsigned int)(len_str2 - len_str1);
    return count;
}

int main(void)
{
    unsigned int count;
    char list1() = "category";
    char list2() = "caterirrrr";

    count = count_edit_dist(list1, list2);
    printf("edit distance between strings: %un", count);
}

entities – How to get the count of referenced entity (by views entityreference result)?

For example, a student can register a class. The class node has a date field indicating whether the class is active for registering. The registration node has an entityreference field referencing to the class node. I get the referencing class nodes by using “Views: Filter by an entity reference view” option. This view has a filter of “current date is between the start and the end date”. Now, if the count of the reference view result rows is 0, the student should be redirected from the node/add/registration page to an explanation page which tells students there is currently no active classes for registering.
On the module entityreference_count page, it says,

Currently this module only works with entityreference fields
configured to use the “Simple (with optional filter by bundle)”
option. It does not work with the “Views: Filter by an entity
reference view” option. This may be addressed in a future release.

How can I make it?

MySQL count rows from other table AND get most recent title

I have a situation which can be boiled down to the following:

TABLE students
COLUMNS id, name, email

TABLE attendances
COLUMNS id, student_id, event_id

TABLE events
COLUMNS id, name, date_of_event

I want to be able to see all the students, and how many events they’ve attended, so I have:

select name, email, count(attendances.id) as number_of_shows_attended from students
left join attendances
   on student_id = students.id
group by name, email

RESULT

Jane Doe | jane@uni.edu | 0
John Doe | john@uni.edu | 10
Will Doe | will@uni.edu | 2

Perfect. Except I also wish to have a final column which lists the most recent event attended (which can be based on the events.date_of_event date column).

I have tried simply adding the missing column, like so:

select name, email, count(attendances.id) as number_of_shows_attended, 
   event.name as most_recent_event_attended from students 
left join attendances
   on student_id = students.id
inner join events
   on attendances.event_id on events.id
group by name, email

It gives me an event name as expected, but how can I ensure it is the most recent one? I tried adding order by events.date_of_event desc, but of course it’s sorting the results of the query.

dnd 5e – In term of game balance, what repercussions should players expect if DM wouldn’t count Grapple and Shove as “an attack”?

This is how Player’s Handbook (p. 194) describes what counts as an attack:

If there’s ever any question whether something you’re doing counts as an attack, the rule is simple: if you’re making an attack roll, you’re making an attack

There are ambiguous exceptions from this rule though — so-called “special attacks”. There are only two of them — Grapple and Shove from the PHB.

As a DM, for the sake of clarity and consistency I want to call Grapple just “an action” or “a contest”, not “special melee attack”. So instead of

you can use the Attack action to make a special melee attack, a grapple

the text of the house rule (or the errata proposal, if you wish) will be

you can use the Attack action to make an action in combat, a grapple

So does for Shove. This is also consistent with the PHB “Contests in Combat” (emphasis mine):

Battle often involves pitting your prowess against that of your foe. Such a challenge is represented by a contest. This section includes the most common contests that require an action in combat: grappling and shoving a creature. The DM can use these contests as models for improvising others.

So the lowercase “attack” is changed to “action” or “contest”. For instance, the next passage in the Grappling description will be “If you’re able to make multiple attacks with the Attack action, this contest replaces one of them”. The rest of the rules remains unchanged.

I want to figure out what possible impact this will make to the game balance. For instance, will some feats/class features change substantially.

In terms of the game mechanics, what consequences/repercussions this change will have? For sanity’s sake let’s confine ourselves to the the Player’s Handbook.

count pairs of matching integers in a collection in Common Lisp

This defines count-pairs which

  • takes a collection of integers
  • returns the number of pairs of integers that are equal to each other.

If the input collection has these integers …

1 2 2 1 1

… then count-pairs returns 2 because

  1. one pair can be made with the 1’s
  2. a second pair can be made with the 2’s
  3. The leftover 1 does not affect the answer.
(defun make-pair (table i)
  (setf
   ;; increment pair count:
   (gethash 'pairs table) (1+ (gethash 'pairs table 0))
   ;; make first half of the new pair unavailable:
   (gethash i table) nil)
  table)

(defun keep-first-half-of-pair (table i)
  (setf (gethash i table) t)
  table)

(defun first-half-available-p (table i)
  (gethash i table))

(defun consider-one (table i)
  (if (first-half-available-p table i)
      (make-pair table i)
      (keep-first-half-of-pair table i)))

(defun count-pairs (integers)
  (let ((table (reduce #'consider-one
                       integers
                       :initial-value
                       (make-hash-table))))
    (gethash 'pairs table 0)))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; boilerplate for running as a hacker rank submission
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(read-line) ;; we don't need n

(defvar integers
  (read-from-string
   (concatenate 'string
                "(" (read-line) ")")))

(format t "~a~%" (count-pairs integers))

Some questions I have:

  1. What changes would make this more idiomatic?
  2. Is there an alternative for setf that returns the modified container instead of the multiple values returned by the storing form for the last altered place? (See how I really want the container as the result of make-pair and keep-first-half-available-p …)
  3. Should I stick with the -p naming convention for predicates? It seems ending with a ? is possible and looks more readable to me.
  4. I guess Emacs knows if is a macro and is indenting it that way?
  5. If there is an entirely different and better way to solve in Common Lisp, please do let me know.

Thanks in advance!

python – Count values of sublists

Let’s say I have a list of lists in Python where each element represents a day and how many products are available in a shop:

l = (
    (a, a, a, b, c),
    (a, a, b, b, c),
    (a, b, b, c, c)
)

I would like to create a summary that shows:

Day 1: a:3, b:1, c:1 
Day 2: a:2, b:2, c:1 
Day 3: a:1, b:2, c:2

I need it in a format that I can then chart the availability of products over time.

I’ve tried this:

from collections import Counter
    
Counter(x for sublist in l for x in sublist)

But that counts the elements up across all lists rather than returning the result for each sublist.

sql server – How can I use COUNT in the same query with ROUND() and AVG()?

I need to write a query where I need to first count the tickets sold for spectacles in a theater, then calculate the average number of tickets sold in each theater and to be round it to two decimal place and these values must to be sort by the number of tickets sold. In principle, I want to find the average number of tickets sold for each theater. For this query I must use AVG() and ROUND(). I tried so many different options, but I have different errors. When I tried to run this, this error appeared :

SQL Error: ORA-00937: not a single-group group function

SELECT
    t.id_theater
    , s.id_spectacle
    , ROUND((AVG(COUNT(tickets.id_ticket))), 2) average_tickets_theatre
FROM
    tickets tick
    , theater t
    , spectacles s
WHERE
    tick.id_spectacle = s.id_spectacle
    AND t.id_theater = s.id_theater
GROUP BY
    t.id_theater
    , s.id_spectacle
ORDER BY
    average_tickets_theatre

I tried to add the HAVING clause, but another error appeared:

SQL Error: ORA-00933: SQL command not properly ended

I’m sure that the problem consists in the fact that I’m trying to use COUNT with ROUND() and AVG(), but I really don’t know what do to at this point and would appreciate any help.