Mysql nested count
mysql,count,aggregate-functions
I think you need to do a different approach for what you want Just select all the bookings between the date and count those in a subquery It should be something like this, but its a bit hard to test with the given information SELECT (SELECT count(*) FROM booking AS...
Excel - list entities (items)
With data in column A, in B2 enter the array formula: =IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$11),0)),"") In C2 enter: =COUNTIF($A$2:$A$11,B2) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. ...
Count unique values based on the newest element in a multidimentional array - PHP
// sort array by date in decreasing order usort($arr, function ($a, $b) { return strtotime($b['date_add']) - strtotime($a['date_add']); }); $lids = array(); // used lids $result = array(); foreach($arr as $item) if (!in_array($item['lid'], $lids)) { // If lid was before do nothing if (isset($result[$item['state']])) $result[$item['state']]++; else $result[$item['state']] = 1; $lids[] =...
Count amount of inputfields with the same name in PHP?
You set $termin_von only once before loop. $termin_counter = 1; while(true) { $termin_von = 'termin'.$termin_counter.'_von'; if(!isset($_POST[$termin_von])) break; echo $termin_counter++; } ...
Add SQL SELECT COUNT result to an existing column as text
You can use the count(*) window function. I would put it in a separate column, but you can do: SELECT [CategoryName] + ' (' + cast(count(*) over (partition by Id) as varchar(255)) + ')', [Slug], [ParentCategoryId], [Id] FROM [Categories] ORDER BY [ParentCategoryId] DESC; EDIT: For two tables, use a JOIN...
shell script for counting replacements
Assuming you want to replace the word 'apple' with 'banana' (exact match) in the contents of the files and not on the names of the files (see my comment above) and that you are using the bash shell: #!/bin/bash COUNTER=0 for file in *.txt ; do COUNTER=$(grep -o "\<apple\>" $file...
Counting the number of pairs in a vector
ng <- length(V)/2 table(sapply(split(V,rep(1:ng,each=2)),paste0,collapse="&")) # -1&-1 -1&1 1&-1 1&1 # 1 1 1 1 Here is a better alternative that also uses split, following the pattern of @MartinMorgan's answer: table(split(V,1:2)) # 2 # 1 -1 1 # -1 1 1 # 1 1 1 ...
Add container DIVs around sections in loop
change if ($count%5 == 0) { echo $post_title; echo $post_content; echo "</div>"; $post_title = $post_content = ''; } to if ($count%5 == 0) { echo '<div class="container">'; echo $post_title; echo "</div>"; echo '<div class="container">'; echo $post_content; echo "</div>"; echo "</div>"; $post_title = $post_content = ''; } ...
Count data per day in a specific month in Postgresql
Here you are: select TO_DATE('01-03-2014', 'DD-MM-YYYY') + g.i, count( case (TO_DATE('01-03-2014', 'DD-MM-YYYY') + g.i) between created_at and coalesce(deleted_at, TO_DATE('01-03-2014', 'DD-MM-YYYY') + g.i) when true then 1 else null end) from generate_series(0, TO_DATE('01-04-2014', 'DD-MM-YYYY') - TO_DATE('01-03-2014', 'DD-MM-YYYY')) as g(i) left join myTable on true group by 1 order by 1; You...
MySQL COUNT on multiple relations
You can use something like this as an example: SELECT t.id , t.name , COUNT(s.id) AS count_staff FROM staff_type t LEFT JOIN staff s ON s.type_id = t.id GROUP BY t.id , t.name To understand what that's doing, you can remove the GROUP BY and the aggregate expression (COUNT) function...
python count files and display max
You could use dictionary: import os exts = {} my_exts = ('pdf', 'doc', 'docx', 'xls', 'xlsx', 'ppt', 'pptx') for file in os.listdir("C:\\Users\\joey\\Desktop\\school\\ICOMMH"): ext = os.path.splitext(file)[1] if ext and ext[1:] in my_exts: exts[ext] = exts.get(ext, 0) + 1 print sorted(exts.items(), key=lambda x: x[1], reverse=True) The output will be: [('.doc', 4), ('.pdf',...
MYSQLI rows calculate is too slow
Make you sure have an index on the column visitor_affiliate: CREATE INDEX aff_index ON visitors_table(visitor_affiliate) ; Then you need one query to get the three results: SELECT visitor_affiliate, COUNT(*) AS tot FROM visitors_table WHERE visitor_affiliate IN ( 'firstuser', 'seconduser', 'thirduser') GROUP BY visitor_affiliate It's usually faster than three queries. If...
SQL Server 2012 Count
This is the solution I came up with update CustomerMaster set NoOfMembers = (select count(*) from CustomerMaster m2 where m2.NewMainCustNo = CustomerMaster.CustNo and m2.CustNo <> CustomerMaster.CustNo) where LongName like 'GroupId:%' Check this SQL Fiddle to see the query in action. However I disagree with your data structure. You should have...
PHP count not working [closed]
Your condition code is total wrong, try below:- if(count($credentials) !== 2){ echo "a"; } else { echo "b"; } ...
How to retrieve the most present pair in groups of three columns, with MySQL?
My inclination is to break all the rows out into pairs of users. Then do the aggregation based on that: select u2, count(*) as total_points from ((select goal_user_id as u1, assist_one_user_id as u2 from goals) union all (select assist_one_user_id, goal_user_id from goals) union all (select goal_user_id, assist_two_user_id from goals) union...
SQL query display min count rows only
If I understand correctly, this query is a bit trickier than it seems. Use a subquery to get the counts per category, using a window function to get the minimum count. Then join this back to the original data: select s.model, s.VIN, s.cost from stock s join (select category, count(*)...
Count the number of consecutive pairs in a vector
r,table,count,split,categories
All consecutive pairs can be represented by two parallel vectors, omitting the last or the first observation x <- V[-length(V)] y <- V[-1] and then cross-tabulating these > xtabs(~ x + y) y x -1 1 -1 7 1 1 0 1 or in slightly different form > as.data.frame(xtabs(~x+y)) x...
Efficiently finding the count of column values for distinct rows in a dataframe in r
r,count,subsetting,memory-efficient
assuming your data.frame is called df, using data.table: library(data.table) setDT(df)[ , .(Freq = .N), by = .(id, value)] using dplyr: libary(dplyr) group_by(df, id, value) %>% summarise(Freq = n()) You should choose one of those two packages (dplyr or data.table) and learn it really thoroughly. In the long run you will...
sql select 5 higest values
sql,sql-server,count,select-query
On SQL Server, simply do this: SELECT TOP 5 * FROM Likes ORDER BY [Count] DESC This assumes that your Likes-table already contains a column named [Count] meaning that you don't need to count the records yourself (which is what COUNT(*) does)....
EXcel COUNT values based on multiple conditions in different ranges
arrays,excel,count,multiple-conditions
It sounds like to me that you are trying to perform a COUNT operation that matches 2 distinct criteria. As you noted the COUNTIF formula takes in a single criteria, well there is a COUNTIFS formula that takes in multiple. Here is what I "think" it would look like with...
COUNTA in current row in an array formula (Google Sheets)
arrays,excel,count,google-spreadsheet,row
The formula should work if you convert the booleans (true or false) returned by LEN(A1:E)>0 to numbers (1 or 0), as Barry already mentioned. This can be done quite easily by wrapping the output of the LEN()-function in an N-function or by preceding it with '--'. So, assuming your data...
Count occurrence of unique variables
Tabulating and collapsing Your example vector is vec <- letters[c(1,2,2,2,3,3,4,5,6)] To get a tabulation, use tab <- table(vec) To collapse infrequent items (say, with counts below two), use res <- c(tab[tab>=2],other=sum(tab[tab<2])) # b c other # 3 2 4 Displaying in two columns resdf <- data.frame(count=res) # count # b...
Regex/Algorithm to find 'n' repeated lines in a file
regex,algorithm,count,find,duplicates
One way is splitting your text based on your n then count the number of your elements that all is depending this counting you can use some data structures that use hash-table like dictionary in python that is much efficient for such tasks. The task is that you create a...
Count multidimensionnal array
As you asked in comment,Please try this:- <?php $array = array( '1'=> array('0'=>"Extension", '1'=> 1, '2'=>"3,00 " ), '2'=> array('0'=>"Physics",'1'=>"1","3,00 " ),'3'=> array('0'=>"Physics",'1'=>1,"6,00 "),'4'=> array('0'=>"Desk",'1'=>4,"127,00 "),'5'=> array('0'=>"assistance",'1'=>1,"12,50 " ),'6'=> array('0'=>"Extension",'1'=>1,"3,00 ")); $count = array(); $i = 0; foreach ($array as $key=>$arr) { // Add to the current group count if...
Excel: Count of unique items while ignoring junk duplicates
Let say your column is column A with IDs from row A2-A26, on A28 try this formula: =SUMPRODUCT((A2:A26<>"")/COUNTIF(A2:A26,A2:A26&"")) It worked for my other project. It doesn't need to create another column or table. ...
SQL COUNT- not a single group group function error
sql,oracle,count,group-by,aggregate-functions
Use a derived table? select sum(cnt) from ( select count(*) as cnt from t_object union all select count(*) as cnt from t_diagram ) dt ...
SPARQL: How do I List and count each data type in an RDF dataset?
Since you've grouped by the datatype of ?o, you know that all the ?o values in a group have the same datatype. You can just sample that to get one of those values, and then take the datatype of it: select (datatype(sample(?o)) as ?datatype) (count(?o) AS ?dTypeCount) where { ?s...
Return frequency of string in MYSQL column with PHP
mysql_query returns a resource on success, or FALSE on error. try this $magic = mysql_query("SELECT COUNT(*) as count FROM table WHERE CODE_STR = '$glitter'"); $row = mysql_fetch_assoc($magic); $count = $row['count']; ...
Why does '12345'.count('') return 6 and not 5?
count returns how many times an object occurs in a list, so if you count occurrences of '' you get 6 because the empty string is at the beginning, end, and in between each letter. Use the len function to find the length of a string....
select all but sort by count in postgresql
postgresql,sorting,table,count
Here you are: select * from myTable order by count(1) over (partition by mySortColumn) desc; For more info about aggregate over () construction have a look at: http://www.postgresql.org/docs/9.4/static/tutorial-window.html...
Mysql count and return just one row of data
mysql,join,count,subquery,having
Please give this query a shoot SELECT COUNT(DISTINCT(u.id)) AS users_count FROM users AS u INNER JOIN ( SELECT user_id, COUNT(DISTINCT profile_option_id) AS total FROM profile_answers WHERE profile_option_id IN (37,86,102) GROUP BY users.id HAVING COUNT(DISTINCT profile_option_id) = 3 ) AS a ON a.user_id = u.id If you have lots of data...
fastest way to compare text file content
python,string,python-3.x,count
You can convert the words to a set, so that the lookups will be faster. This should give a good performance boost to your program, because looking up a value in a list has to traverse the list one element at a time (O(n) runtime complexity), but when you convert...
While using string.count() method I am getting an error, TypeError: expected a character buffer object
Don't call the method on the str class but on your string object: s = "That that is is that that is not is not is that it it" sub = "s" print "s.count(sub, 4, 40) : ", s.count(sub, 4, 40) ...
PostgreSQL Order by not working, it doesn’t returns values according to the order
postgresql,count,group-by,sql-order-by
Sometimes, it is easier to use subqueries: select p.*, (select count(*) from scores s where p.id_players in (s.winner, s.loser) ) as GamesPlayed, (select count(*) from scores s where p.id_players in (s.winner) ) as GamesWon from players p order by GamesWon desc; If the maximum of the round is the number...
Select ID having maximum count of another ID
In MySQL this group=wise maximum it is sadly not a simply as you want it to be. Here's a way to do it using a method similar to what is suggested in ROW_NUMBER() in MySQL SELECT a.* FROM ( SELECT playerid ,typeid ,COUNT(*) playcount FROM plays GROUP BY playerid,typeid )...
How to get count from another table (zero when no data) using single query in mysql?
Using sub query you can achieve that... SELECT OFFERED.offered_id, OFFERED.data, (SELECT COUNT(JOINED.joined_id) FROM JOINED WHERE JOINED.offered_id = OFFERED.offered_id) AS count_joined FROM OFFERED ...
MySql : issue with GROUP BY and COUNT clauses
The problem here is you want to count by a different grouping than you want to display. One way around this is two have the counting in a subquery: SELECT DISTINCT product, type FROM mytable WHERE product IN (SELECT product FROM mytable GROUP BY product HAVING COUNT(DISTINCT type) > 1)...
I need to find the longest word in a sentence using JS
try this: Phrase: <input type="text" id="input1" name="LongestWord" placeholder="Put Phrase Here"> <br> <input type="button" id="btn1" value="get Longest Word"> <br/> Longest Word: <span id='sp1'></span> <script> var btn = document.getElementById("btn1"); var in1 = document.getElementById("input1"); var sp1 = document.getElementById("sp1"); btn.onclick = function(){ var vals = in1.value.split(' '); var val = vals[0]; vals.forEach(function(v){ if(v.length>val.length) val...
R — frequencies within a variable for repeating values
You can try library(data.table)#v1.9.4+ setDT(yourdf)[, .N, by = A] ...
Count from associate table
ruby-on-rails,activerecord,count
You can do the following: @total_user_active = Idee.distinct.count(:user) ...
MongoDB - distinct and count, the simplest case
mongodb,count,distinct,mongolab
That command text box is for providing JSON-formatted parameters to whatever command is selected in the drop-down. aggregate is not one of the listed commands, so it appears that you'd need to install the MongoDB shell locally and then remotely connect to your MongoLab database and run the command. See...
count cells quantity with a special contained word and calculate the percentage
This should be achievable simply with COUNTIF. https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34 Formulas: E1 =SUM($E$2:$E$10000) E2 downwards as needed: =COUNTIF($B:$B,$D2) F1 downwards as needed: =$E1/$E$1 ...
Count multiple columns mysql
If you want it on a single row similar to Anirudh's response try this: SELECT SUM(A='Value1'), SUM(B='Value1'), SUM(C='Value1'), SUM(D='Value1') FROM TableName Alternatively to have it the way you specified you can use this: SELECT 'A', SUM(A='Value1') FROM TableName UNION SELECT 'B', SUM(B='Value1') FROM TableName UNION SELECT 'C', SUM(C='Value1') FROM TableName...
LINQ: Count Users in 3 way join
var UsersPerBU = from bu in BusinessUnitsOrgUnits join org in OrgUnits on bu.OrgUnitID equals org.OrgUnitID join u in Users on org.OrgUnitID equals u.OrgUnitID group bu by bu.BusinessUnitID into g select new { BusinessUnitID = g.Key, UserCount = g.Count() }; ...
SQL SELECT COUNT GROUP BY - error
Simple rule: never use commas in the from clause. Always use explicit joins: SELECT t.KategoriID, ad, odendi, COUNT(odendi) FROM taksit t INNER JOIN ogrenci o ON o.KategoriID = t.KategoriID WHERE odendi = 0 GROUP BY t.KategoriID; You should also use table aliases and qualify all column names....
Oracle COUNT() function
select count(*) DAYs FROM ( select trunc(ADD_MONTHS(sysdate,-1),'MM') + level -1 Dates from dual connect by level <= ADD_MONTHS(trunc(sysdate,'MM'),1)-1 - trunc(sysdate,'MM')+1 ) Where To_char(dates,'DY') NOT IN ('SA','SO') ...
MySQL count rows with same values
Conditional aggregation: SELECT user_email, SUM(CASE WHEN post_type LIKE '%reply%' THEN 1 ELSE 0 END) AS Replies, SUM(CASE WHEN post_type LIKE '%topic%' THEN 1 ELSE 0 END) AS Topics FROM `wp_users` INNER JOIN `wp_posts` ON wp_users.id = wp_posts.post_author WHERE post_type LIKE '%topic%' OR post_type LIKE '%reply%' GROUP BY user_mail ...
Count Number of Objects in Java
You can use this abstract class, in order to count any type of objects that inherits it. The answer is based on addy2012's answer (Thanks!): public abstract class Countable { private static final Map<Class<?>, Integer> sTotalCounts = new HashMap<>(); public Map<Class<?>, Integer> getCountsMap() { return sTotalCounts; } public int getTotalCount()...
Count Table Record Value and Assign Count Value as Variable in PHP mySQL
You should try this, surely will solve your query. $qry = "SELECT state_name, COUNT(district) As TotalData FROM area_info GROUP BY state_name"; ...
C code Count elements in array that are not null
First of all, char s*[]={"one","two",NULL,NULL,five,"",""}; doesn't compile. Did you mean char* s[]={"one","two",NULL,NULL,"five","",""}; Secondly, I assume you call your function using inUse(s, 7); /* OR */ inUse(s, sizeof(s) / sizeof(*s)); Thirdly, you should change if(s !=NULL) to if(s[i] != NULL) since you want to check if individual elements of the array...
Understanding the issues of bit copying [closed]
class blueberry is just some class that has a static int value blQuantity that increments in the constructor each time an object is created, and decrements in the destructor each time an object goes out of scope. Are you sure that's each and every time one is created? I...
Record count after distinct
Something like this you mean SELECT * FROM ( SELECT a.*, rownum row_num FROM ( select field1, field2, ... COUNT(DISTINCT field1) OVER () RESULT_COUNT from table1 where condition1 order by someField desc )a WHERE rownum < :maxRow )WHERE row_num > = :minRow; ...
Adding to ListView columns not increasing column count
c#,winforms,listview,count,control
The reason for this behaviour is that the ListView Columns do not provide you with a regular matrix or grid like a Table or a DataGridView. Instead they are what you would call a 'Jagged Array', meaning, that not all rows have the same number of columns. So while you...
Javascript constructor function to count the number of instances
javascript,object,constructor,count
As I understand it correctly, the countInstances variable is added to the prototype and will act like a static copy for all the instances and will act as my counter. No, it will be, in effect, a default value for instances, not a "static." If you put it on...
SQL display months when Count is 0 [duplicate]
sql,date,count,sql-server-2008-r2,datepart
Create a set of Months as the first table in the from clause, and join your query to this. Then you will get a result for every month. I have similar issues with financial reporting where I need results for all months and financial years. I have used the DATENAME...
Trying to use output of one function to influence the next function to count words in text file
python,file,python-3.x,io,count
The problem is quite simple: You split it into two function, but you completely ignore the result of the first function and instead calculate the number of words before the cleanup! Change your main function to this, then it should work. def main(): harper = readFile("Harper's Speech.txt") newWords = cleanUpWords(harper)...
Select and create month row and count the data - MySQL
I use your own query because it's working and I modified it a bit. Consider the query below: SELECT `month`, `total_visits` FROM ( SELECT * FROM ( SELECT '01' AS `month_num`, 'Jan' As `month`, 0 AS `total_visits` UNION SELECT '02' AS `month_num`, 'Feb' AS `month`, 0 AS `total_visits` UNION SELECT...
How to count business days from a given date in SQL Server using a calendar reference table
sql-server,date,count,calendar
Reword the question to "Of the next @count bankdays after @start_date, which one occurs last?" SELECT @end_date = MAX(date) FROM ( SELECT TOP(@count) date FROM Calendar WHERE date > @start_date AND kindofday = 'bankday' ORDER BY date ) t ...
minimum date among several columns
Something like this will get what you want: SELECT LEFT(FirstIssued, 6) AS YYMM, COUNT(DISTINCT Policy_No) AS NumPolicies FROM ( SELECT Policy_No, MIN(issue_date) AS FirstIssued FROM table_a WHERE indicator = 'fln' GROUP BY Policy_No ) A GROUP BY LEFT(FirstIssued,6) The key is to first find the min date for each policy,...
Counting up “broadly”: Not 0,1,2,..,9,10,11,..,99 but 00, 01, 10, 11, 02, 12, 20, 21, 22, 03, .., 99
math,count,language-agnostic,numbers,sequence
So first you want all numbers with the digits 0 in order. i.e. just 00 Then all numbers with the digits 0,1: 00, 01, 10, 11 but excluding 00 Then all numbers with digits 0,1,2: 00, 01, 02, 10, 11, 12, 20, 21, 22 but excluding 00, 01, 10, 11...
Saving the click counts of an item each item it is clicked
javascript,php,count,save,value
What you should do is to store them in a Database. You should send the id and the current count of the item to the server, check the current count and existence of the item by querying database, and if validated, save them to the database (update or insert, depending...
Count() function in mysql
You need to do the counts without joining, otherwise you're counting the number of rows in the result of the join. SELECT (SELECT COUNT(*) FROM user_legislation_notifications WHERE user_id = '7' and isRead = '0') + (SELECT COUNT(*) FROM user_petition_notifications WHERE user_id = '7' and isRead = '0') AS user_pet_notif_count ...
Multiple joins with ordered by count ignores where conditions
Your FROM clause is mixing old style joins and new style joins Instead try: FROM view_count JOIN video_index ON view_count.video_id = video_index.id JOIN category_video_rel ON category_video_rel.video_id = video_index.id ...
SQL Server : select count of Todays Transactions
GETDATE() will return the current date and time. You may then use a CAST() or CONVERT() to strip the time value and be left with just the date, ie. SELECT CONVERT(VARCHAR(10), GETDATE(), 110) The above code would return 05-19-2015 for today. Select Count(pm1.number) As number From SCenter.probsummarym1 As pm1 Where...
PHP - Count how many days ago
Step 1 : Remove the Last part of your date i.e., CET or CE $Date = substr($Date, 0, strpos($Date, " ")); It will the character after last spaces Step 2 Find the Difference between current date and the extracted date by $interval = $datetime1->diff($datetime2); And here's the entire code :...
Counting word occurrences in a file C
Must read question: Why is “while ( !feof (file) )” always wrong? Thanks to Jonathan Leffler's comment. Please check my comments in the code below. I got you a start up for when the words are appearing once. I am letting the rest of the job for you, so that...
Count Distinct Number of Email Addresses in the Results of a Query in MS Access
I do not know of any way to incorporate it in your query directly, but the following query should get you the number of unique email addresses using the same conditions and everything from your orginal query: SELECT COUNT(*) AS NumEMails FROM (SELECT DISTINCT UsersTABLE.EmailAddress FROM ClassSessionsTABLE INNER JOIN (UsersTABLE...
MySql - count from beginning until each day
This could be done using user defined variable in mysql and then get the running total as select id, total, date date from ( select id, @tot:= @tot+total as total, date from my_table,(select @tot:=0)x order by date )x ...
PHP - Create unique id number for each item
<?php $images = get_post_meta(get_the_ID(), "images", true); $images = unserialize($images); foreach($images as $image) { $ar[] = array("order" => $image['order'], "img_id" => $image['image_id'], "desc" => $image["desc"]); } asort($ar); //Create count variable $i=0; foreach($ar as $item) { $image_id = $item['img_id']; $media_med = wp_get_attachment_image_src( $image_id, "medium", false); $media_full = wp_get_attachment_image_src( $image_id, "full", false); //assign...
MySQL COUNT vs SELECT rows performance
microtime(true) is your PHP friend. If there is an index starting with gameid, "Approach 1" might be faster, since it can do the COUNT(*) in the index. On the other hand, there are two things that count against it: 3 queries is usually slower than 2. If there are a...
Getting the count of the second index in a muti-dimensional array PHP
php,arrays,multidimensional-array,count
You are trying to count $array which is one. If you are looking for counting array index, use $arrayCount = count($array[0]); Hope it helps....
How to change the character count result of a PHP query?
Try this: http://jsfiddle.net/Twisty/dhbcdLL1/ Using JQuery, you can do this. function truncate(t, l) { if (t.length > l) { var parts = []; parts[0] = t.substr(0, l - 3); parts[1] = t.substr(l - 3); return parts[0] + "..." + "<span style='display: none;'>" + parts[1] + "</span>"; } else { return t;...
SQL Several Count
mysql,sql,if-statement,count,conditional-statements
Solution > SELECT COUNT(DISTINCT CASE WHEN MinProduto = 1 AND MaxProduto = 1 > THEN PRENUMERO END) AS QtdCombustivel > ,COUNT(DISTINCT CASE WHEN MinProduto <> 1 AND MaxProduto <> 1 THEN PRENUMERO END) AS QtdLoja > ,COUNT(DISTINCT CASE WHEN MinProduto = 1 and MaxProduto <> 1 THEN PRENUMERO END) AS...
excel count/sum stop count/sum match?
The INDEX function can provide a valid cell reference to stop using a MATCH function to find an exact match on 0. This formula is a bit of a guess as there was no sample data to reference but I believe I have understood your parameters. =SUMIFS(C2:index(C2:C35, match(0, A2:A35, 0)),...
MySQL Multiple Conditions on Group By / Having Clause
php,mysql,count,group-by,having-clause
You can do it by joining on the total category counts, and then using conditional aggregation: select modulecategory, count(case when requireall = 'yes' then if(s = t, 1, null) else s end) from ( select modulecategory,empname, requireall, count(*) s, min(q.total) t from employeeskill e inner join modulecategoryskill mcs on e.skillid...
MySQL Conditional count based on a value in another column
mysql,count,distinct,condition
One approach is to use a correlated subquery. Just get the list of ranks and then use a correlated subquery to get the count you are looking for: SELECT r.rank, (SELECT COUNT(DISTINCT t2.id) FROM myTable t2 WHERE t2.rank >= r.rank ) as cnt FROM (SELECT DISTINCT rank FROM myTable) r;...
SQL count date time
Yes you will need subquery: select * from (select someExpression as Requiredby FROM [dbo].[Main] where Location = 'Home' and ScriptTypeID = '1') t where Requiredby < GETDATE() But I really think you want this: select sum(case when cast(cast(DischargeDatenew as date) as datetime) + cast(DischargeTime as time) < getdate() then 1...
Divide columns values by total rows in impala
It looks like you could just calculate the total number of distinct sample_ids*2 in advance, and then use that for the subsequent query, since that value doesn't change per row. If the value did depend on the row, you might want to take a look at the analytic/window functions available...
Count number of occurences in repeated variables (r)
Would something like this make sense? table(unlist(data)) View(table(unlist(data))) Var1 Freq Friday 6 Monday 6 Not working at all 1 Saturday 2 Scheduled working days not relevant 0 Sunday 2 Thursday 7 Tuesday 6 Wednesday 6 ...
Simple Table with dplyr on Sequence Data
You just have to convert your data to a data.frame to use dplyr and then you can easily get your desired output: require(dplyr) # ungrouped data_frame(var = c(dta)) %>% group_by_("var") %>% summarise(n()) ## var n() ## 1 acquaintance 1 ## 2 alone 2 ## 3 child 17 ## 4 notnotnot...
Show All and Count Duplicates MySQL
You can use a correlated subquery: SELECT id, name, (SELECT COUNT(*) FROM mytable AS t1 WHERE t1.name = t2.name) AS cnt FROM mytable AS t2 Demo here ...
Display Count(*) in PHP
Your are getting the total of all by count, this is a grouped result. mysqli_num_rows gives the total rows, but because it's one row you get 1 as result. This will solve it: <?php $sql = mysqli_query ($connect,"SELECT * FROM w2 WHERE Statut_Cde LIKE '%LIV%';"); $result = mysqli_num_rows($sql); ?> <h2><b>...
Get column value on count (MySQL/Java)
rsMetaData.getColumnCount(); As the method says this will return the number of columns which in your query is 1 (the count one column). To get the desired data simply assign a name on the count fetch the result. To do this add the desired name ResultSet rs = st.executeQuery("SELECT COUNT(*) as...
Sort and counting a column without uniq in bash
I would do this in awk. But as Aaron said, it will require reading the input twice, since the first time you hit a particular line, you don't know how many other times you'll hit it. $ awk 'NR==FNR{a[$1]++;next} {print a[$1],$0}' inputfile inputfile This goes through the file the first...
Return rows where a field has a specified count
You first need to find out which groups have a count of three rows. You can do that by using a GROUP BY and HAVING clause: SELECT id FROM myTable GROUP BY id HAVING COUNT(*) = 3; The HAVING clause is used for conditions regarding aggregation, do not use the...
Continual counter regardless of page refresh
javascript,php,jquery,html,count
Check this DEMO //Counter var counter=22000000000; if(typeof(localStorage.getItem('counts'))!='object') { counter=parseInt(localStorage.getItem('counts')); } setInterval(function () { $(".count").html(counter); ++counter; localStorage.setItem('counts',counter); }, 1000); Highlight on localStorage localStorage is an implementation of the Storage Interface. It stores data with no expiration date, and gets cleared only through JavaScript, or clearing the Browser Cache / Locally Stored...
New Column with query result in SQL Server
Just use window functions for these calculations: SELECT DISTINCT tmp.Arrival, tmp.Flight, COUNT(*) OVER (PARTITION BY Flight) as NumPassengers, SUM(CASE WHEN SegmentNumber = 1 AND LegNumber = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY Flight, Arrival) ) as NumLocalPassengers, STD, STA FROM #TempLocalOrg tmp; ...
Counting all .class files
Use wc -l. wc stands for "word count", and its -l flag makes it count lines. Since your command outputs a line per file1, counting lines means counting files. find . -type f -name '*.class' | wc -l 1 please note @chepner's comment...
Pivot Tables or Group By for Pandas?
python,pandas,count,group-by,pivot-table
You could use pd.crosstab() In [27]: df Out[27]: Col X Col Y 0 class 1 cat 1 1 class 2 cat 1 2 class 3 cat 2 3 class 2 cat 3 In [28]: pd.crosstab(df['Col X'], df['Col Y']) Out[28]: Col Y cat 1 cat 2 cat 3 Col X class...