spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Avoid subquery and 2nd group by?


I think you can do this as an aggregation query with a join:

select t1.CountryId, count(distinct
t2.CompanyId)
from table1 t1 join
     table2 t2
     on t1.CountryID = t2.CountryID and
t1.CompanyID = t2.CompanyID
where t2.AttributeId = 1
group by t1.CountryId;

Categories : SQL

Related to : Avoid subquery and 2nd group by?
How to write this query (containing group by and join in subquery) in Laravel?
There are so many possibilities. That's one: DB::table('channels') ->join('programs', 'channels.id', '=', 'programs.channel_id') ->select('channels.id', 'channles.name', DB::raw('count(programs.id) as num_of_programs')) ->groupBy('channels.id', 'channels.name') ->get(); I hope it works fine for you.

Categories : Mysql
MySQL aggregate function with GROUP BY to avoid NULL's overwriting desired field value
Just aggregate your results using MAX: SELECT lesson_number, MAX(CASE WHEN dayofweek = 0 THEN lesson_date END) AS 'MONDAY', MAX(CASE WHEN dayofweek = 1 THEN lesson_date END) AS 'TUESDAY', MAX(CASE WHEN dayofweek = 2 THEN lesson_date END) AS 'WEDNESDAY', MAX(CASE WHEN dayofweek = 3 THEN lesson_date END) AS 'THURSDAY', MAX(CASE WHEN dayofweek = 4 THEN lesson_date END) AS 'FR

Categories : Mysql
How to change subQuery to Co-related Subquery?
I think this is what you're looking for. Just move the query into the FROM section and use a JOIN to filter the results based on the same conditions you had in the WHERE clause, except this time you reference the result set (RS) returned by the subquery. SELECT FirstName, LastName FROM AdventureWorks.Person.Contact a INNER JOIN [AdventureWorks].[HumanResources].[Employee] b ON a.ContactID

Categories : SQL
EC2 security group setting for load balancer, auto scaling group
OutofService indicates that your Elastic Load Balancer is either not ready, or the instances are failing their Health Check. If you point to the little "i" information icon, it will explain why an instance is not InService. Within your Elastic Load Balancer, take a look at the Health Check tab and confirm that it is configured correctly. It will either be checking a URL (eg /index.htm) or checkin

Categories : Amazon
ggplot2 line chart gives "geom_path: Each group consist of only one observation. Do you need to adjust the group aesthetic?"
You get this error because one of your variables is actually a factor variable . Execute str(df) to check this. Then do this double variable change to keep the year numbers instead of transforming into "1,2,3,4" level numbers: df$year <- as.numeric(as.character(df$year)) EDIT: it appears that your data.frame has a variable of class "array" which might cause the pb. Try then: df <- da

Categories : R
Recently Add
SQL command to insert two primary keys into third table
How to do a registerform by ASP.NET VB SQL
Shortest time and trip between two stops across many trip options (GTFS)
Using IN and EXISTS subquery with AND operation instead of OR?
How to select multiple alias columns from the same table
Delete records from a given table
date manipulation in postgresql
PSQL query with join query from a db newbie
Protect LocalDB from user access
Update field with combined contents from other fields
SQL select unique records under certain priority and conditions from multiple columns
Multiply quantities for all parent child relationships
I'm having an issue running a query from a batch file
Concatenate strings while filtering by another column in SQL Server view
Parsing Dynamic XML to SQL Server tables with Parent and child relation
Using MERGE for delete and insert in oracle
how to display the pre value and post value of a column after updating it?
sql loop to fill up date between two time stamps
With SQL/TSQL, how can I use STUFF within this WHILE clause?
Challenging db table and query
Query to fetch row corresponding to Max value in SQL
Oracle SQL - ORA-00936 on DATE()
how to split single row into multiple row in db2?
Combine fields into one column
How can I perform an SQl where query then list properly?
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.