Styjun

Took a trip to a parallel universe, need help deciphering

What does it mean to describe someone as a butt steak?

Does a druid starting with a bow start with no arrows?

Why is Collection not simply treated as Collection<?>

Theorems that impeded progress

Neighboring nodes in the network

Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?

Is it possible to download Internet Explorer on my Mac running OS X El Capitan?

Will google still index a page if I use a $_SESSION variable?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

Famous Pre Reformation Christian Pastors (Non Catholic and Non Orthodox)

Why doesn't H₄O²⁺ exist?

How to say in German "enjoying home comforts"

Etiquette around loan refinance - decision is going to cost first broker a lot of money

How could indestructible materials be used in power generation?

Is it canonical bit space?

Does casting Light, or a similar spell, have any effect when the caster is swallowed by a monster?

Doing something right before you need it - expression for this?

How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?

In Romance of the Three Kingdoms why do people still use bamboo sticks when papers are already invented?

Is it possible to create light that imparts a greater proportion of its energy as momentum rather than heat?

A reference to a well-known characterization of scattered compact spaces

Why is the 'in' operator throwing an error with a string literal instead of logging false?

What's the point of deactivating Num Lock on login screens?

How can I get a count of highest orders in my table? [duplicate]

count top 10 most occuring values in a column in mysqlHow can I prevent SQL injection in PHP?How to list the tables in a SQLite database file that was opened with ATTACH?Get list of all tables in Oracle?How can I get column names from a table in SQL Server?How to get a list of MySQL user accountsHow can I do an UPDATE statement with JOIN in SQL?How to get the sizes of the tables of a MySQL database?IBM DB2 looking to get a unique count of each duplicate item in a table and list it with another value of the tableHow to retrieve all the data stored in different tables in a MySQL database?get all customer payment based on order id

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

This question already has an answer here:

• 
  count top 10 most occuring values in a column in mysql
  
  4 answers
  
  

pretty new to sql and I'm looking for some insight. We have customers that associate a "customer_type" to them. I was looking to grab all the customers with that specific type and output the top 10 highest customers that have ordered from us with that type. I was able to do that but I'm confused on where to go from here to get that count.

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customers T1
INNER JOIN sales_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
ORDER BY
T1.customer_id,
T1.customer_name,
T2.order_id

So basically this is outputting something like this:

customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer B = customer name B = type = order_id B
customer B = customer name B = type = order_id B
customer C = customer name C = type = order_id C

etc. etc. What can I do to consolidate that and just find out the top 10 with most order_ids?

mysql sql

share|improve this question

edited Mar 21 at 21:58

Gordon Linoff

794k37318421

asked Mar 21 at 21:58

Brandon Brandon

858

marked as duplicate by Shadow mysql
Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Mar 21 at 22:03

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hint: GROUP BY.
  
  – Gordon Linoff
  Mar 21 at 21:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Gordon would you be able to help me further? I tried GROUP BY but it's still doing the same thing. Am I missing a count or sum somewhere in here?
  
  – Brandon
  Mar 21 at 22:14
  

  
  
  
  

add a comment | 

0

This question already has an answer here:

• 
  count top 10 most occuring values in a column in mysql
  
  4 answers
  
  

pretty new to sql and I'm looking for some insight. We have customers that associate a "customer_type" to them. I was looking to grab all the customers with that specific type and output the top 10 highest customers that have ordered from us with that type. I was able to do that but I'm confused on where to go from here to get that count.

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customers T1
INNER JOIN sales_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
ORDER BY
T1.customer_id,
T1.customer_name,
T2.order_id

So basically this is outputting something like this:

customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer B = customer name B = type = order_id B
customer B = customer name B = type = order_id B
customer C = customer name C = type = order_id C

etc. etc. What can I do to consolidate that and just find out the top 10 with most order_ids?

mysql sql

share|improve this question

edited Mar 21 at 21:58

Gordon Linoff

794k37318421

asked Mar 21 at 21:58

Brandon Brandon

858

marked as duplicate by Shadow mysql
Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Mar 21 at 22:03

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hint: GROUP BY.
  
  – Gordon Linoff
  Mar 21 at 21:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Gordon would you be able to help me further? I tried GROUP BY but it's still doing the same thing. Am I missing a count or sum somewhere in here?
  
  – Brandon
  Mar 21 at 22:14
  

  
  
  
  

add a comment | 

This question already has an answer here:

• 
  count top 10 most occuring values in a column in mysql
  
  4 answers
  
  

pretty new to sql and I'm looking for some insight. We have customers that associate a "customer_type" to them. I was looking to grab all the customers with that specific type and output the top 10 highest customers that have ordered from us with that type. I was able to do that but I'm confused on where to go from here to get that count.

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customers T1
INNER JOIN sales_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
ORDER BY
T1.customer_id,
T1.customer_name,
T2.order_id

So basically this is outputting something like this:

customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer B = customer name B = type = order_id B
customer B = customer name B = type = order_id B
customer C = customer name C = type = order_id C

etc. etc. What can I do to consolidate that and just find out the top 10 with most order_ids?

mysql sql

share|improve this question

edited Mar 21 at 21:58

Gordon Linoff

794k37318421

asked Mar 21 at 21:58

Brandon Brandon

858

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customers T1
INNER JOIN sales_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
ORDER BY
T1.customer_id,
T1.customer_name,
T2.order_id

customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer A = customer name A = type = order_id A
customer B = customer name B = type = order_id B
customer B = customer name B = type = order_id B
customer C = customer name C = type = order_id C

share|improve this question

edited Mar 21 at 21:58

Gordon Linoff

794k37318421

asked Mar 21 at 21:58

Brandon Brandon

858

share|improve this question

edited Mar 21 at 21:58

Gordon Linoff

794k37318421

asked Mar 21 at 21:58

Brandon Brandon

858

edited Mar 21 at 21:58

Gordon Linoff

794k37318421

edited Mar 21 at 21:58

Gordon Linoff

794k37318421

asked Mar 21 at 21:58

Brandon Brandon

858

asked Mar 21 at 21:58

Brandon Brandon

858

1 Answer
1

active

oldest

votes

0

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

shows only one entry per customer for me oO and if you want the amount of sales :

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*)
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

share|improve this answer

edited Mar 21 at 22:45

answered Mar 21 at 22:03

jonny h.jonny h.

34

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yeah it just orders it by that. Doesn't solve the problem of multiple companies with multiple order ID's all stating them individually
  
  – Brandon
  Mar 21 at 22:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  oh youre looking for groupby => sometihng like GROUP BY T2.order_id
  
  – jonny h.
  Mar 21 at 22:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried that also. Although it does force me to add everything in the SELECT part to the GROUP BY part. But the result still prints out the same duplicate customer names with multiple order_ids. I don't know how to consolidate that. Like if customer_name has 10 order_id's, how do i make it show just that instead of having to manually count it?
  
  – Brandon
  Mar 21 at 22:15
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id FROM customer T1 INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_type = 'R' GROUP BY customer_id ORDER BY customer_id shows only one entry per customer for me oO and if you want the amount of sales : SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*) FROM customer T1 INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id GROUP BY customer_id ORDER BY customer_id
  
  – jonny h.
  Mar 21 at 22:26
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Odd.. I’ll try again tomorrow since I left for the day. But thank you for the help!
  
  – Brandon
  Mar 21 at 22:36
  

  
  
  
  

add a comment | 

0

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

shows only one entry per customer for me oO and if you want the amount of sales :

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*)
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

share|improve this answer

edited Mar 21 at 22:45

answered Mar 21 at 22:03

jonny h.jonny h.

34

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yeah it just orders it by that. Doesn't solve the problem of multiple companies with multiple order ID's all stating them individually
  
  – Brandon
  Mar 21 at 22:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  oh youre looking for groupby => sometihng like GROUP BY T2.order_id
  
  – jonny h.
  Mar 21 at 22:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried that also. Although it does force me to add everything in the SELECT part to the GROUP BY part. But the result still prints out the same duplicate customer names with multiple order_ids. I don't know how to consolidate that. Like if customer_name has 10 order_id's, how do i make it show just that instead of having to manually count it?
  
  – Brandon
  Mar 21 at 22:15
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id FROM customer T1 INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_type = 'R' GROUP BY customer_id ORDER BY customer_id shows only one entry per customer for me oO and if you want the amount of sales : SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*) FROM customer T1 INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id GROUP BY customer_id ORDER BY customer_id
  
  – jonny h.
  Mar 21 at 22:26
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Odd.. I’ll try again tomorrow since I left for the day. But thank you for the help!
  
  – Brandon
  Mar 21 at 22:36
  

  
  
  
  

add a comment | 

0

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

shows only one entry per customer for me oO and if you want the amount of sales :

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*)
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

share|improve this answer

edited Mar 21 at 22:45

answered Mar 21 at 22:03

jonny h.jonny h.

34

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yeah it just orders it by that. Doesn't solve the problem of multiple companies with multiple order ID's all stating them individually
  
  – Brandon
  Mar 21 at 22:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  oh youre looking for groupby => sometihng like GROUP BY T2.order_id
  
  – jonny h.
  Mar 21 at 22:12
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried that also. Although it does force me to add everything in the SELECT part to the GROUP BY part. But the result still prints out the same duplicate customer names with multiple order_ids. I don't know how to consolidate that. Like if customer_name has 10 order_id's, how do i make it show just that instead of having to manually count it?
  
  – Brandon
  Mar 21 at 22:15
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id FROM customer T1 INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_type = 'R' GROUP BY customer_id ORDER BY customer_id shows only one entry per customer for me oO and if you want the amount of sales : SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*) FROM customer T1 INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id GROUP BY customer_id ORDER BY customer_id
  
  – jonny h.
  Mar 21 at 22:26
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Odd.. I’ll try again tomorrow since I left for the day. But thank you for the help!
  
  – Brandon
  Mar 21 at 22:36
  

  
  
  
  

add a comment | 

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

shows only one entry per customer for me oO and if you want the amount of sales :

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*)
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

share|improve this answer

edited Mar 21 at 22:45

answered Mar 21 at 22:03

jonny h.jonny h.

34

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

SELECT T1.customer_id, T1.customer_name, T1.customer_type, T2.order_id ,COUNT(*)
FROM customer T1
INNER JOIN sale_orders T2 ON T1.customer_id = T2.customer_id
WHERE T1.customer_type = 'R'
GROUP BY customer_id
ORDER BY customer_id

share|improve this answer

edited Mar 21 at 22:45

answered Mar 21 at 22:03

jonny h.jonny h.

34

answered Mar 21 at 22:03

jonny h.jonny h.

34

answered Mar 21 at 22:03

jonny h.jonny h.

34