w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Select distinct values from tables in sql
Use distinct in your count() SELECT * FROM `student_discipline` a LEFT JOIN `discipline` b ON a.disc_id = b.id LEFT JOIN `student` c ON a.stud_id = c.id WHERE a.mark > 4 GROUP BY c.id HAVING COUNT(distinct b.id) >= 5

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

Select distinct values of distinct group
You can use the following code to get your desired array with only 1 SQL executed $sql = "SELECT title, group_concat(year) as years FROM table_name group by title" $rs = mysql_query($sql); $arr = array(); while($row = mysql_fetch_assoc($rs)) { $arr[$row['title']] = explode(',',$row['years']); }

Categories : PHP

Compare 2 columns in 2 tables with DISTINCT value
It looks like the USER_ID value is the concatenation of your ORG_ID and something to make it unique. I'm assuming this is from a COTS product and nothing a human would have built. Your desire is to find out how many entries there are by department. In SQL, when you read the word by in a requirement, that implies grouping. The action you want to take is to get a count and the reserved word for that is COUNT. Unless you need something out of the TBL_ORG, I see no need to join to it SELECT LEFT(T.USER_ID, 3) AS USER_CREATED , COUNT(1) AS GroupCount FROM TBL_USER AS T GROUP BY LEFT(T.USER_ID, 3) Anything that isn't in an aggregate (COUNT, SUM, AVG, etc) must be in your GROUP BY. SQLFiddle I updated the fiddle to also show how you could link to TBL_ORG if you need an elemen

Categories : SQL

Need logic for joining two tables and using distinct
The below solution meets your needs select T2.[index],Avg(T1.[Current]) as [Current] ,avg(t1.[bpo]) as bpo,avg(t1.[Forecast]) as [Forecast] from Table1 T1 join Table2 T2 on isnull(T1.[type],'') = t2.[type] and isnull(t1.code,'') = t2.code group by T2.[index]

Categories : SQL

SQL select single distinct values from multiple columns (not combined values)
Try to use union with both columns select Department1 from tab union select Department2 from tab NOTE: union command eliminates duplicates

Categories : SQL

Join two tables and show distinct records
As you are joining on the PART_ID column, the result you are getting is what you would expect to get. For example, there are two C0001 in the LOG-OUT table and these will join to the same C0001 in the PART RETURN table as there is nothing to distringuish the rows in the return table from each other: PART LOG-OUT TABLE -> PART RETURN TABLE C0001 07/29/2013 501 -> C0001 09/04/2013 STORE C0001 10/01/2013 605 -> C0001 09/04/2013 STORE You need another criteria to join on, or you need to join on some more unique identifier in order to achieve the results you are looking for.

Categories : Ms Access

SQL: JOIN two tables with distinct rows from one table
You can do something like this: In first CTE - assign rankings for VirtualLicenses within the Product groups. In second CTE - assign rankings for Licensce within the Product groups (order by exp. date) And at the end just join the two subqueries on productID and ranking. WITH CTE_VL AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY vl.VirtualLicenseId ASC) RN FROM dbo.VirtualLicense vl LEFT JOIN dbo.Product p ON vl.ProductName = p.Name ) ,CTE_License AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY ExpiryDate ASC) RN FROM dbo.License ) SELECT VirtualLicenseId, LicenseId FROM CTE_VL vl LEFT JOIN CTE_License l ON vl.ProductId = l.ProductID AND vl.RN = l.RN SQLFiddle DEMO

Categories : SQL

SQL Server DISTINCT OR GROUP BY WITH TEMP TABLES
Try using the first query you listed as a subquery, and join it to tblapsdata. It will look something like this: DECLARE @CustomerID int = 8, @UtilityCompanyID int = 1 SELECT MD.SERV_ACCT, AD.ACCOUNT, MD.MAXINVDATE FROM (SELECT DISTINCT SERV_ACCT, MAX(INV_DATE) as MAXINVDATE FROM tblAPSData WHERE SERV_ACCT NOT IN (SELECT ServiceAccount FROM tblMEP_Meters JOIN tblMEP_Sites ON tblMEP_Meters.SiteID = tblMEP_Sites.ID JOIN tblMEP_Projects ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID WHERE CustomerID = 8 AND Type = 1 ) AND ACCOUNT IN (SELECT AccountNumber FROM tblMEP_CustomerAccounts WHERE CustomerID = @CustomerID AND UtilityCompanyID = @Util

Categories : SQL

SQL select from two tables, using a distinct value between the two IF it exists, otherwise only using value from the first table?
Try a LEFT JOIN instead. I assume you need to group by room as well: SELECT DISTINCT roomList.room, userlist.room, roomList.totalDesks, COUNT(DISTINCT userlist.desk) AS desksUsed, roomlist.totalDesks-COUNT(DISTINCT userlist.desk) AS desksOpen FROM roomlist LEFT JOIN userlist ON roomlist.room=userlist.room GROUP BY roomList.room, userlist.room, roomList.totalDesks

Categories : SQL

Select distinct totals from access tables
I assume the three tables each have two columns: Run_Date and Total Defects, Not Closed or Closed. If you have a table with all the run dates in it, something like this will work: SELECT RunDates.Run_Date, T_D.[Total Defects], T_O.[Not Closed], T_C.[Closed] FROM (( RunDates LEFT JOIN T_C ON RunDates.Run_Date = T_C.Run_Date) LEFT JOIN T_O ON RunDates.Run_Date = T_O.Run_Date) LEFT JOIN T_D ON RunDates.Run_Date = T_D.Run_Date If not, you will need to construct one using a UNION (not UNION ALL): SELECT RunDates.Run_Date, T_D.[Total Defects], T_O.[Not Closed], T_C.[Closed] FROM (( (SELECT Run_Date FROM T_C UNION SELECT Run_Date FROM T_O UNION SELECT Run_Date FROM T_D) AS RunDates LEFT JOIN T_C ON RunDates.Run_Date = T_C.Run_Date) LEFT JOIN T_O ON R

Categories : Ms Access

Distinct doesn't work when joining two tables
Correct. distinct returns distinct of all the fields. If you want distinct EmployeeIds, then try something like: SELECT distinct e.EmployeeID FROM Employee AS e INNER JOIN DealerSessions AS ds ON e.EmployeeID = ds.EmployeeID WHERE (ds.SessionID = 154) The additional fields are resulting in multiple rows. If you want one row, then you can do something like this: select EmployeeID, Name, Surname, DealerSessionsID, SessionID, Expr1 from (SELECT e.EmployeeID, e.Name, e.Surname, ds.DealerSessionsID, ds.SessionID, ds.EmployeeID AS Expr1, row_number() over (partition by e.EmployeeId order by (select NULL)) as seqnum FROM Employee AS e INNER JOIN DealerSessions AS ds ON e.EmployeeID = ds.EmployeeID WHERE (ds.SessionID = 154) ) t where seqnum =

Categories : SQL

How to get core data distinct values with relationship values?
The property used as sectionNameKeyPath must be included in the propertiesToFetch: [req setPropertiesToFetch:@[@"identityID", @"sortname", @"contact.sectionIndex"]];

Categories : IOS

Comma separated values from SQL, getting distinct values and their count
How about: var result = from word in entries.SelectMany(e => e.HowFound.Split(',')) group word by word into gr select new {Entry = gr.Key, Count = gr.Count()}; grouping instead of distinct allows you to get the count.

Categories : C#

Entity Framework Distinct records from multiple tables
Well this should work without many changes, though i would have wrote it entirely in expressions: var distinctValues = (from a in dataContext.A_Table join b in dataContext.B_Table on a.EmpID equals b.EmpID join c in dataContext.C_Table on b.SomeID equals c.ID where a.IsActive == true && a.ID == id select new NewClass() { ID = c.ID, Name = c.Name }).ToList() .GroupBy(x=>new {ID = x.ID,Name = x.Name}) .Select(x=>new {ID = x.Key.ID,Name = x.Key.Name});

Categories : C#

get the average from two distinct columns joining other tables in MySQL
This is your answer. but you should conside now month is aug not march b/c i used now(). okey. so change your table data accordingly and check the result. select AVG( loc) as loc,AVG(qty) as qty from tbl1 where month NOT IN (concat(UCASE(DATE_FORMAT(date_add(now(), INTERVAL -1 MONTH),'%b')),',', UCASE(DATE_FORMAT(date_add(now(), INTERVAL -2 MONTH),'%b')),',', UCASE(DATE_FORMAT(date_add(now(), INTERVAL -3 MONTH),'%b')))) group by type For DEMO Click here:-http://sqlfiddle.com/#!2/2a15e/15

Categories : Mysql

Join two tables and display distinct list of column
A join will do: SELECT s.skill_name, CASE WHEN c.candidate_id IS NOT NULL THEN '(X)' ELSE '()' END has_skill FROM skills s LEFT JOIN CandidateToSkillMap c ON s.skill_id = c.skill_id WHERE c.candidate_id = 1 If it joins the right side successfully and thus c.candidate_id will be not null, then it means the person has that skill and you mark it else - don't.

Categories : PHP

How to select only distinct result based on particular creteria from tables using join
This should help get you on your way. You need to use a where clause. SELECT a.callnumber, b.callnumber FROM table a INNER JOIN table b ON a.callnumber=b.callnumber WHERE a.callduration > value AND b.callduration > value.

Categories : Mysql

Nesting a Select Distinct and returning all results from Joined tables
use Plantation select distinct tblmember_v62.Acct, tblmember_v62.First, tblmember_v62.Last, tblMemberAccount_v62.Balance from tblmember_v62, tblMemberAccount_v62 Where dbo.tblMember_v62.MemberId = tblmemberaccount_v62.MemberId and tblMemberAccount_v62.Balance > '0.00' and tblMember_v62.BillingNotify = '2' and (tblMemberAccount_v62.InsertDate > '2012-01-01 00:00:00.000' or tblMemberAccount_v62.UpdateDate > '2012-01-01 00:00:00.000') Order by dbo.tblMemberAccount_v62.MemberId, tblmember_v62.Last

Categories : SQL

Best way to get distinct values
Try the distinct query, if your field is indexed you can use this: https://developers.google.com/appengine/docs/python/ndb/queries#projection query_set = cls.query(projection=["field"], distinct=True) set_of_field = [data.field for data in query_set] But if you have a huge list you can either do this in a taskqueue and store the results somewhere or just keep a distinct data in another model.

Categories : Google App Engine

Trying to sum distinct values SQL
You can do the following: SELECT SUM(distinct [Ship Cost]) . . . But, I don't recommend this. You could have two items with the same cost and only one would be counted. The better way is to select one value for each Tracking #, using the row_number() function: select SUM(case when seqnum = 1 then [Ship Cost] end) from (select t.*, row_number() over (partition by [Order #], [Tracking #] order by (select NULL) ) as seqnum . . . ) t

Categories : SQL

Selecting multiple values of other column basing on distinct values of column on same table
You can use the LISTAGG function as of Oracle 11g R2. e.g. SELECT deptno , LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;

Categories : SQL

JOINing 3 tables, SUM() values from 2 of the tables (MYSQL)
You should be able to use the following code which uses subqueries with both aggregates to get the result: select i.item_id, i.item_name, coalesce(p.TotalBought, 0)TotalBought, coalesce(v.vote_count, 0) vote_count from items i left join ( select item_id, count(*) TotalBought from purchases where purchase_status = 'bought' group by item_id ) p on i.item_id = p.item_id left join ( select item_id, sum(vote) vote_count from votes group by item_id ) v on i.item_id = v.item_id; See SQL Fiddle with Demo. You could also write this without two subqueries but you will need to place the WHERE filter of purchase_status on the JOIN: select i.item_id, i.item_name, count(p.item_id) TotalBought, coalesce(v.vote_count, 0) vote_count from items i left join purchases p o

Categories : Mysql

Get distinct values from table
Maybe you need to restart your rails server: https://github.com/rails/rails/issues/7399 Previous similar SO question Update: From the docs: When using pluck, "The values has same data type as column" Change loop to: <% @count.each %> do |i| <%= i %> <% end %>

Categories : Ruby On Rails

XSL 1.0 distinct values from a template
You're very close with the key approach, the trick is that you need to include the identity of the parent node as part of the grouping key: <xsl:key name="children" match="child" use="concat(generate-id(..), '|', .)" /> <xsl:template name="uniqueChildren"> <xsl:param name="parent" /> <xsl:for-each select="$parent/child[generate-id() = generate-id( key('children', concat(generate-id($parent), '|', .))[1])]"> <xsl:value-of select="." /> </xsl:for-each> </xsl:template> This creates key values of the form "<id-of-parent>|apple", "<id-of-parent>|orange", etc. Edit: in your comment you say "In my actual data, the child nodes aren't a direct child of the parent nodes. There's 2 levels between parent and child, e.g.

Categories : Xml

regarding distinct values in sql query
Try with GROUP BY like SELECT Id, Code, newCode, Course, Internship FROM courses WHERE Year ='c' AND (Internship ='IS' OR Internship = '') GROUP BY Code ORDER BY newCode ASC

Categories : PHP

Not able to get DISTINCT column values :
I wasn't be able to test it, but apart from possible minor syntax adjustment, this should help you: $visited = Visit::model()->findAllByAttributes( array('to_user_id'=>Yii::app()->user->id), array('distinct' => True) ); According to the doc, findAllByAttribute accept a condition or criteria parameter as second argument. If this is an array (according the doc for find()), it will serve to initialize the various criteria properties of the requests.

Categories : PHP

XSLT 1.0 How to get distinct values
Solution <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <xsl:variable name="test"> <xsl:call-template name="delimitedvalues"> <xsl:with-param name="paramvalues" select="//category" /> </xsl:call-template> </xsl:variable> <xsl:call-template name="distinctvalues"> <xsl:with-param name="values" select="$test" /> </xsl:call-template> </xsl:template> <xsl:template name="distinctvalues"> <xsl:param name="values"/> <xsl:variable name="firstvalue" select="substring-before($values, ',')"/> <xsl:variable name="restofvalue" select="subst

Categories : Misc

Query to get distinct values
try this select * from `order` where c1 not in (select c1 from `order` where c2 = 20) group by c1 **NOTE : that order is mysql reserved keyword , so you should escape it by backticks DEMO HERE C1 C2 2 80 3 80 5 80

Categories : Mysql

XSLT 1 display distinct values
This XSLT stylesheet: <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text"/> <xsl:template match="NewDataSet"> <xsl:apply-templates select="Vehicle[not(ManufacturerName=preceding-sibling::Vehicle/ManufacturerName)]"/> </xsl:template> <xsl:template match="Vehicle"> <xsl:value-of select="ManufacturerName"/> <xsl:text>&#xa;</xsl:text> </xsl:template> </xsl:stylesheet> produces the following output when applied to your example XML: FORD CHEVROLET VAUXHALL The important part is the select in the xsl:apply-templates. It works by only selecting Vehicle elements whose Manufacturer has not appeared in one of the preceding Vehicle elements at the

Categories : Xslt

Selecting distinct values from a JSON
As you can see here, when you have more values there is a better approach. http://jsfiddle.net/MsYGJ/ temp = {} // Store each of the elements in an object keyed of of the name field. If there is a collision (the name already exists) then it is just replaced with the most recent one. for (var i = 0; i < varjson.DATA.length; i++) { temp[varjson.DATA[i].name] = varjson.DATA[i]; } // Reset the array in varjson varjson.DATA = []; // Push each of the values back into the array. for (var o in temp) { varjson.DATA.push(temp[o]); } Here we are creating an object with the name as the key. The value is simply the original object from the array. Doing this, each replacement is O(1) and there is no need to check if it already exists. You then pull each of the values out and repopulat

Categories : Javascript

MYSQL Select two distinct values
SELECT v.id, v.make_id, v.model_id, min(i.attach_location), mk.make, md.model, v.made_year, u.username FROM wsq_garage_vehicles v, wsq_garage_vehicles_gallery vg, wsq_garage_images i, wsq_garage_makes mk, wsq_garage_models md, wsq_users u WHERE v.id = i.vehicle_id AND mk.id = v.make_id AND md.id = v.model_id AND v.user_id = u.user_id ORDER BY v.date_updated DESC group by v.id, v.make_id, v.model_id, mk.make, md.model, v.made_year, u.username LIMIT 10 Furthemore you can not query i.attach_location. In result your query would easy: SELECT v.id, v.make_id, v.model_id, mk.make, md.model, v.made_year, u.username FROM wsq_garage_vehicles v, wsq_garage_vehicles_gallery vg, wsq_garage_images i, wsq_garage_makes mk, wsq_garage_models md, wsq_users u WHERE v.id = i.vehicle_id AND mk.id = v.make_i

Categories : Mysql

Writing only distinct values to an array
You can try this way. Sort the array first. 4 4 5 5 6 6 7 First element would always be distinct. Copy it in second array. Now, start from second element in sorted array, and copy after comparing with one element present in second array. If sorted[i]==output[j] , ignore or say increment i. Else, copy ith element to output array, increment both j and i Do this while you completely traversed your sorted array. This will do the trick. int[] arr = {4,4,5,5,6,6,7}; int out[]= {0}; // or may be some size int i=1; out[0] = arr[0]; int j=0; while(i<arr_length){ if(arr[i]!=out[j]){ out[++j]=arr[i]; } i++; } for(int k=0;k<out_length;k++){ printf("%d,",out[k]); }

Categories : C

Loop through distinct values in SSRS
Try LookUpSetfor your field =Fields!csg_name.Value Heres the documentation for it: http://msdn.microsoft.com/en-us/library/ee240819.aspx

Categories : SQL

Linq - select distinct values
List<MyObject> l = new List<MyObject>(); //fill the list Dictioonary<string,string> d = l .Select ( x=>new { x.vendor, x.unit } ) //Get only the properties you care about. .Distinct() .ToDictionary ( x=>x.vendor, //Key selector x=>x.unit //Value selector );

Categories : Linq

distinct-values() function won't work in XSL
Well distinct-values was introduced in 2007 in XPath version 2.0 and that way is only supported by XSLT 2.0 processors like Saxon 9 or AltovaXML or XmlPrime. XSLT 1.0 processors like Xalan or libxslt/xsltproc or the various XSLT implementation in the major browsers don't support that function. So you need to either change to an XSLT 2.0 processor or you need to use a different approach like Muenchian grouping with your XSLT 1.0 processor.

Categories : Xml

Counting Distinct values from Sql Table
You would add a where clause into the subquery: SELECT Userid, (case when [YES] is null then 0 else [YES] end) as Completed, (case when [NO] is null then 0 else [NO] end) as Pending, (case when [YES] is null then 0 else [YES] end + case when [NO] is null then 0 else [NO] end ) as Total FROM (SELECT Userid, SurveyStatus, COUNT(ParcelId) as cnt FROM ParcelAllocationsurvivor WHERE DateAllocated >= '2013-08-01' and DateAllocated <='2013-08-07' and SurveyType = 'Survey1' group by Userid, SurveyStatus ) AS p PIVOT(max(cnt) FOR surveystatus IN([YES],[NO])) AS pvt order by Userid; By the way, the select distinct are redundant. You do not need them for this query.

Categories : Mysql

Update column with distinct values
This will get you the id|name combinations from the source table where the id appears only once: select id, name from source where name is not null and name <> '' -- use trim() too if appropriate group by id having count(name) = 1 and assuming your target table is empty you can just insert those rows. You can then remove those inserted rows from the source table. What remains after that in your source table are the ids that appear there more than once, and names that are null, and they have to be cleaned up first, i.e. reduced to a set of unique (id,name) combinations. How to do that would depend on the varieties of garbage in the source table (e.g. nulls, zero-length strings, leading/trailing spaces, misspellings and corrections, exact duplicates, etc). P.S. substitute th

Categories : Sql Server

indexing distinct values for a column in SQL
See SQL Fiddle -- set up tables create table Authors ( id int(11) not null auto_increment, name varchar(64), primary key(id) ); create table Books ( id int(11) not null auto_increment, name varchar(64), author varchar(64), primary key(id) ); -- populate Authors table, giving each author a unique ID insert into Authors (name) select distinct author from Books; -- Add an author_id column to the Books table alter table Books add author_id int(11) after author; -- Update the Books table, filling in the author_ids update Books b, Authors a set b.author_id = a.id where b.author = a.name; -- If you don't care to retain the author name to author ID mapping, -- you can then drop the Authors table.

Categories : Mysql

Distinct values from List of objects
First, implement the equality operator (this goes into class Chromosome): public class Chromosome : IEquatable<Chromosome> { public bool[][] body { get; set; } public double fitness { get; set; } bool IEquatable<Chromosome>.Equals(Chromosome other) { // Compare fitness if(fitness != other.fitness) return false; // Make sure we don't get IndexOutOfBounds on one of them if(body.Length != other.body.Length) return false; for(var x = 0; x < body.Length; x++) { // IndexOutOfBounds on inner arrays if(body[x].Length != other.body[x].Length) return false; for(var y = 0; y < body[x].Length; y++) // Compare bodies if(body[x][y] != other.body[x][y

Categories : C#



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