w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
MSQL select regardless of distinct or not distinct (count greater than zero)
In the data in the question, there is no repeating id in table A. The query should be returning only one row from A, then. However, your query has an error. The second join should be back to the first table, not the second: SELECT a.col1, b.col1, c.col2 FROM a LEFT JOIN b ON b.id = a.id LEFT JOIN c ON c.id = a.id WHERE a.id = 1 You had c.id = b.id and b.id could be NULL if there is no match in that table. Also, from your description, I wonder if a union would be the best approach: select a.col1, 'a' from a where id = 1 union all select b.col1, 'b' from b where id = 1 union all select c.col2, 'c' from c where id = 1

Categories : Mysql

Distinct value of a column and do a count on each distinct value
Considering the following example: To get the unique values, and the occurrences of each unique value, you can do the following: The image is tiny, so here is are the formulas to type-in and drag down: FORMULAS 1. Type in the first cell of column B: =IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0))),"",INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0))) Drag the formula down the column B. You will get a list of unique values. 2. Type in the first cell of column C: =COUNTIF($A$2:$A$8,B2) Drag the formula down the column C. You will get the number of occurrences of each unique value. RESULT This is what you will get:

Categories : Excel

Mysql count and count distinct in same query
COUNT(DISTINCT field) will do the job: SELECT user_id, job.job_id AS job_id, job_link, COUNT(job.click_ip) AS click_ip, COUNT(DISTINCT job.click_ip) AS click_ip_distinct FROM user JOIN job ON user.job_id = job.job_id WHERE user.user_id =7 GROUP BY job_id SQL Fiddle here: http://sqlfiddle.com/#!2/5e03e/2/0

Categories : PHP

Sql Distinct & count
When pid, pname and description are the same (like in your example) then group by will aggregate those results to one row. select t.pid, t.pname, t.description, sum(t.qty) as qty from tbltemptransaction t group by t.pid, t.pname, t.description Edit select t.pid, sum(t.qty) as qty from tbltemptransaction t where pid = @userid group by t.pid @user id is a parameter for session("userid") Final Edit select t.pid, p.pname, p.description, sum(t.qty) as qty from tbltemptransaction t inner join tblproducts p on p.pid = t.pid where t.userid = 3 group by t.pid, p.pname, p.description

Categories : SQL

How to Echo a Distinct Count in PHP?
count(distinct style) is the only row and column returned. Try this: $result = mysql_query("select count(distinct style) from bottles", $con); $row = mysql_fetch_row($result); $num_rows = $row[0];

Categories : PHP

How to COUNT DISTINCT on more than one column
I don't know if it's the best way, but I normally concatenate the two values, using a delimiter to enforce "distinctness", so they become one expression, which Oracle can handle with COUNT DISTINCT: SELECT "group_id",count(*) , count(distinct "p_id" || '-' || "version") FROM tbl group by "group_id"

Categories : SQL

Distinct count for two columns
You need a GROUP BY like this:- select Category, COUNT(*) as Count from YourTable GROUP BY Category; SQLFIDDLE

Categories : Mysql

Count the distinct words
If you don't mind a slight variation in the given output then the traditional approach is with sort and uniq: $ sort file | uniq -c 2 com.a.b 1 com.c.d 3 com.e.f Otherwise use a scripting language that has associative arrays such as awk: $ awk '{a[$0]++}END{for(k in a)print k,a[k]}' file com.e.f 3 com.c.d 1 com.a.b 2 Pipe to sort for ordering: $ awk '{a[$0]++}END{for(k in a)print k,a[k]}' file | sort com.a.b 2 com.c.d 1 com.e.f 3

Categories : Bash

Select distinct value when count = 1 in SQL
If you use a where clause then you would filter out other values what would make the count inaccurate. select fID_a from tbl group by fID_a having count(*) = 1 and sum(case when fID_b = 1 then 1 else 0 end) = 1

Categories : SQL

Count distinct same names
It's very basic SQL example, group by column + aggregating results select FirstName, count(*) from Table1 group by FirstName

Categories : SQL

Get Distinct Rows Where Value in Row is Count > 1?
Use Enumerable.First: var GetSetRows = from DataRow dr in MyTable.Rows group dr by dr.Field<string>("SetNumber") into grp where grp.Count() > 1 select grp.First();

Categories : C#

sum of distinct value count from one table
if you have known number of degree, this could be done using static query, SELECT Name, SUM(CASE WHEN degree = 11 THEN 1 ELSE 0 END) `degree 11`, SUM(CASE WHEN degree = 12 THEN 1 ELSE 0 END) `degree 12`, SUM(CASE WHEN degree = 13 THEN 1 ELSE 0 END) `degree 13` FROM tableName GROUP BY Name SQLFiddle Demo OUTPUT ╔════════╦═══════════╦═══════════╦═══════════╗ ║ NAME ║ DEGREE 11 ║ DEGREE 12 ║ DEGREE 13 ║ ╠════════╬═══════════╬═══════════╬═══════════╣ ║ Adam ║ 2 ║ 1 ║ 0 ║ ║ Beny ║ 1 ║ 0 ║ 1 ║

Categories : Mysql

How to SELECT COUNT DISTINCT?
UPDATED SELECT COUNT(DISTINCT ROUND(CONVERT(DECIMAL(20, 4), DATEDIFF(minute, CONVERT(datetime, '2013-01-01 00:00:00', 120), CONVERT(datetime, date + ' ' + time, 120))) / 5, 0)) n FROM table1 WHERE date = '2013-06-22' AND errcode = 'Scrubber Failure' AND frequency = 1 Output: | N | ----- | 6 | Here is SQLFiddle demo.

Categories : SQL

Distinct count with multiple columns
It sounds like you want to count only the records with minimum Relevancy for each ID. Therefore, you can do (SQL Fiddle example): SELECT Category, COUNT(1) FROM Table1 t1 WHERE NOT EXISTS ( SELECT 1 FROM Table1 t2 WHERE t2.ID = t1.ID AND t2.Relevancy < t1.Relevancy ) GROUP BY Category

Categories : Mysql

SQL Server 2008 Count(Distinct?
You probably want to treat dates as dates, rather than strings. You can determine whether a particular timestamp is on a Friday -- or the hour of a timestamp -- using DATEPART, without having to CONVERT it into a CHAR: datename(weekday, timestamp_value) -- returns Friday datepart(weekday, timestamp_value) -- returns either 5 or 6, depending on the value of SET DATEFIRST. (Get Day of Week in SQL 2005/2008) datepart(hour, timestamp_value) -- returns hour part Using these, you can test whether a timestamp is on Friday at or after noon by checking if datepart(weekday, timestamp_value) = 6 and datepart(hour, timestamp_value) >= 12. bendataclear pointed out that you're using distinct on a case statement which can only ever return 0 or 1, so your total will only ever be 0, 1, or 2. If

Categories : SQL

Select Count Distinct Syntax, vb.net
MONTH(date) in mysql returns string. So you need to enclose with ' '. sRetrieve = "SELECT COUNT (DISTINCT defect_code) FROM daily_data WHERE MONTH(date)='" & select_month & "' AND YEAR(date)=" & select_year;

Categories : Mysql

Aggregate distinct count in LINQ - VB.NET
Since PagedDataSource doesn't implement IEnumerable<T>, just IEnumerable, you need to first use Cast to cast the sequence to a sequence of the proper type. You'll need to know what the actual type of the objects in the sequence are as you haven't mentioned what it is here. Then just apply each operation one after the other: (In C#:) var query = table.Cast<YourTypeGoesHere>() .Select(item => item.Column) .Distinct() .Count();

Categories : C#

Count distinct elements and return only one
I think the easiest is to join another select statement to get the total number of rows and add that to your main SELECT statement. SELECT data AS data, count.count FROM data JOIN (SELECT COUNT(DISTINCT(data)) AS count FROM data WHERE parameter = "test") AS count WHERE parameter = "test" ORDER BY rand() LIMIT 1; Here's a SQL Fiddle

Categories : Mysql

Query to get count of distinct value in MySQL
select count(distinct column_whose_distinct_values_you_want_to_count) from rbt_activation_details_log where RADL_ACTIVE ='A' group by RADL_REQUEST_MSISDN You're grouping and counting on the same column, so this will always give you 1 as result. EDIT: Then simply omit the group by clause select count(distinct RADL_REQUEST_MSISDN) from rbt_activation_details_log where RADL_ACTIVE ='A'

Categories : Mysql

Pandas aggregate count distinct
How about either of: >>> df date duration user_id 0 2013-04-01 30 0001 1 2013-04-01 15 0001 2 2013-04-01 20 0002 3 2013-04-02 15 0002 4 2013-04-02 30 0002 >>> df.groupby("date").agg({"duration": np.sum, "user_id": pd.Series.nunique}) duration user_id date 2013-04-01 65 2 2013-04-02 45 1 >>> df.groupby("date").agg({"duration": np.sum, "user_id": lambda x: x.nunique()}) duration user_id date 2013-04-01 65 2 2013-04-02 45 1

Categories : Python

Scope of COUNT(DISTINCT ..) when used with GROUP BY
This may actually depend on where DISTINCT is used. For example, SELECT DISTINCT COUNT( would be different than SELECT COUNT(DISTINCT. In this case, it will work as you want and get a count of distinct names in each group (even if the names are not distinct across groups).

Categories : Mysql

Hibernate HQL Count Distinct not working?
This is how to do in Hibernate Criteria Criteria crit = session.createCriteria(Rating.class) .add(Restrictions.like("item", item) .addOrder(Order.asc("user")) .setProjection( Projections.distinct(Projections.projectionList() .add(Projections.property("user"), "user"))) .setResultTransformer(Transformers.aliasToBean(Rating.class));

Categories : Java

SQL/Hive count distinct column
Yes, it is almost correct. But you have one simple mistake. Your column name is wrong inside COUNT. SELECT columnA,columnB,COUNT(DISTINCT columnC) No_of_distinct_colC from table_name group by columnA,columnB

Categories : SQL

mysql COUNT DISTINCT multiple columnes with WHERE
Are you looking for the count per pid? If so, this is a simpler query: SELECT pid, SUM(location like '%a%') as As, SUM(location like '%b%') as Bs, SUM(location like '%c%') as Cs FROM db.t WHERE (date > @dt) GROUP BY pid

Categories : Mysql

Select distinct column, and a count of the entries
CREATE TABLE tbl (`email` varchar(10)); INSERT INTO tbl (`email`) VALUES ('a@b.com'), ('b@b.com'), ('c@b.com'), ('d@b.com'), ('e@b.com'), ('a@b.com'), ('b@b.com'), ('c@b.com'), ('c@b.com'); SELECT email, COUNT(*) FROM tbl GROUP BY email; Result | EMAIL | COUNT(*) | ---------------------- | a@b.com | 2 | | b@b.com | 2 | | c@b.com | 3 | | d@b.com | 1 | | e@b.com | 1 | See a demo

Categories : Mysql

How to display Distinct Count in Access Textbox?
Here's a link that loops through the selected records based on your parameters from the Select statement. It uses the .RecordCount off a recordset, but the kicker is the recorde must be accessed first (hence the loop).

Categories : SQL

How to implement SQL query, max(count(distinct attribute_name))
Try SELECT * FROM ( SELECT book_publisher, COUNT(DISTINCT book_author_id) author_count FROM table1 GROUP BY book_publisher ORDER BY author_count DESC ) WHERE rownum = 1 or WITH cte AS ( SELECT book_publisher, COUNT(DISTINCT book_author_id) author_count FROM table1 GROUP BY book_publisher ) SELECT book_publisher FROM cte WHERE author_count = ( SELECT MAX(author_count) FROM cte ) or with analytical functions SELECT book_publisher, author_count FROM ( SELECT book_publisher, COUNT(DISTINCT book_author_id) author_count, DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT book_author_id) DESC) rank FROM table1 GROUP BY book_publisher ) WHERE rank = 1 Here is SQLFiddle demo

Categories : SQL

Count Number of Distinct Columns in a Row with tSQL
Select Name,Count(*) From (select Name,FieldA from test10 Union select Name,FieldB from test10 union select Name,FieldC from test10 Union select Name,FieldD from test10)as a Group by name

Categories : SQL

How to count distinct values in a sequence of four numbers from 0 to 10?
A Set<Integer> such as a HashSet<Integer> could simplify things since it would not allow duplicates. It will allow you to call the add method on duplicates to the Set, but if the Set already contains the item, the add method will do nothing other than return false. Getting the size() of the set after adding all items, will tell you how many non-duplicate items exist.

Categories : Java

Show count of columns distinct values
You need to use the iif function within the two additional columns. Here is some pseudo code to get you started. SELECT County ,sum(iif(TGTE = "L",1,0)) as [L_Count] ,sum(iif(TGTE = "H",1,0)) as [H_Count] FROM MyTable GROUP BY COUNTY;

Categories : C++

Get Count() for DISTINCT values for each field SQL Server
You don't need the rank function, you can use a normal count with TOP 10: SELECT TOP 10 phonenumber, [count] = COUNT(*) FROM ozekiout GROUP BY Phonenumber ORDER BY [count] DESC; If you want to include more than 10 results if there are ties e.g. Phonenumber count 01111111111 18 01111111112 15 01111111113 15 01111111114 14 01111111115 13 01111111116 13 01111111117 12 01111111118 12 01111111119 10 01111111120 10 01111111121 10 01111111122 10 .... CUT OFF 01111111122 9 you can use: SELECT TOP 10 WITH TIES phonenumber, [count] = COUNT(*) FROM ozekiout GROUP BY Phonenumber ORDER BY [count] DESC;

Categories : SQL

Oracle/SQL - Need help optimizing COUNT DISTINCT query
Firstly, verify that the columns you use in your WHERE clauses (and from the COUNT DISTINCT) have indexes (or are primary/unique/foreign keys). Secondly, check if you could add other WHERE clauses to replace the DISTINCT in the COUNT, or even replace it with a COUNT(*) which is very optimized. Thirdly, you could try to move your count(distinct) to another join or even a subquery and check if performances changes.

Categories : SQL

Count distinct value pairs in multiple columns in SQL
Get all distinct id, name and address columns and count the resulting rows. SELECT COUNT(*) FROM mytable GROUP BY id, name, address

Categories : SQL

R: count number of distinct values in a vector
Here are a few ideas, all points towards your solution already being very fast. length(unique(x)) is what I would have used as well: x <- sample.int(25, 1000, TRUE) library(microbenchmark) microbenchmark(length(unique(x)), nlevels(factor(x)), length(table(x)), sum(!duplicated(x))) # Unit: microseconds # expr min lq median uq max neval # length(unique(x)) 24.810 25.9005 27.1350 28.8605 48.854 100 # nlevels(factor(x)) 367.646 371.6185 380.2025 411.8625 1347.343 100 # length(table(x)) 505.035 511.3080 530.9490 575.0880 1685.454 100 # sum(!duplicated(x)) 24.030 25.7955 27.4275 30.0295 70.446 100

Categories : R

Getting Count Only of Distinct Value Combinations of multiple fields.
The subquery/CTE method is the "right" way to do it. A quick (in terms of typing but not necessarily performance) and dirty way is: select count(distinct customername+'###'+Planet) from #Customer; The '###' is to separate the values so you don't get accidental collisions.

Categories : SQL

Count number of Distinct days in query
You can CAST as date SELECT COUNT(DISTINCT CAST(StartDate AS DATE)) FROM Stats WHERE StartDate >= '20130101' AND StartDate < '20140101' Also use an unambiguous date format such as yyyymmdd and >= < not BETWEEN. Your current query would include the 31st December if there was a row with exactly the value 20131231 00:00:00 but not any with different times on that date. I doubt that is intentional.

Categories : Sql Server

SQL Server 2008 Count(Distinct CASE?
You can't really count half things using count, so that is not the way to go. But, you can do it with arithmetic. I think something like this: select (count(distinct (case when DATENAME(weekday, a.ApptStart) <> 'Friday' then cast(a.apptstart as date) end) ) + 0.5 * count(distinct (case when DATENAME(weekday, a.ApptStart) = 'Friday' then cast(a.apptstart as date) end) ) ) as ScheduleDays If the docs only work on Fridays for half a day, I don't think you need to check for the time of the appointment. Of course, you can if you like by adding it into the second count. Note that to count days, I used the simpler syntax

Categories : SQL

Trying count distinct strings, whats going wrong
The problem is not your read loop. It gets stuck in an infinite loop in while(i!=input.size()){ Think carefully about your end condition, and the lines that change i: i+=count2;continue; i++; Will i++ ever be executed? Will i ever exactly equal input.size()? If there are repeated words? If there are not? . . . Ok, thought about it? I replaced that loop with this. The comments should explain: while(i < input.size()) { // Store current string distinct.push_back(input[i]); // Skip same strings int j = i; while ( j < input.size() // Don't go too far && input[i] == input[j] ) // And only while duplicates ++j; // Store a count of how many we skipped count.push_back(j-i); // Move to the next *non-identical* st

Categories : C++

MySQL 5.6.13 count distinct result not match
Because COUNT DISTINCT doesn't consider rows where exists a column value NULL I try the same query with my table. In my table in some rows there are column with NULL value, so the result is different. See this sample SQLFiddle

Categories : Mysql

MySQL - Getting count of distinct values from 3 tables
Just UNION the tables together and COUNT. The UNION will remove duplicates, so a simple COUNT will do the trick - a COUNT DISTINCT won't be needed. This will work if you want the count only; if you want other values the query gets slightly more involved: SELECT COUNT(*) FROM ( SELECT employee_no FROM complete_shifts WHERE date = '2013-06-13' UNION SELECT employee_no FROM incomplete_shifts WHERE date = '2013-06-13' UNION SELECT employee_no FROM incomplete_shift_register WHERE date = '2013-06-13' ) AllShifts

Categories : Mysql



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.