SQL Find the number of orders in each line count

user1489597

I have a SQL question from one of the well known IT company couple month ago when they were interviewing me, and I never got it figured out.

An order can have multiple lines - For ex., if a customer ordered cookies,

chocolates, and bread, this would count as 3 lines in one order. The question

is to find the number of orders in each line count. The output of this query

would be something like 100 orders had 1 line, 70 orders had 2 lines, 30 had 3

lines, and so on. This table has two columns - order_id and line_id

 Sample Data:
 order_id   line_id
 1      cookies
 1      chocolates
 1      bread
 2      cookies
 2      bread
 3      chocolates
 3      cookies
 4      milk

desired output:

 orders line
 1       1
 2       2
 1       3

So generally speaking, we have a very large data set, and the line_id per order_id can be ranging from 1 to infinite(Theoretically speaking).

 The desired output for the general case is:

 orders line
 100    1
  70    2
  30    3
  etc..

How can I write a query to find the total number of orders per line count=1,2,3... etc

My thought on this problem is to first subquery the count of line_id per order_id.

And then select the subquery along with a list of values as the second column ranging from 1 to max(lines_id per order)

 Test Data:

 create table data
 (
 order_id int,
 line_id char(50)   
 );


 insert into data
 values(1,  'cookies'),
 (1,    'chocolates'),
 (1,    'bread'), 
 (2,    'bread'),
 (2,    'cookies'),
 (3,    'chocolates'),
 (3,    'cookies'),
 (4,    'milk');


 Since order_id=1 has 3 lines,
 order_id=2 has 2 lines,
 order_id=3 has 2 lines,
 order_id=4 has 1 line.

 Thus it yield our solution:

 orders line
 1       1
 2       2
 1       3

 This is because both order_id = 2 and 3 has 2 lines. So it would mean 2 orders has line = 2.

So far, I have:

 select lines,
 sum(case when orders_per_line = '1' then 1 else 0),
 sum(case when orders_per_line = '2' then 1 else 0),
 sum(case when orders_per_line = '3' then 1 else 0)
 from(
 select lines, order_id, count(*) as orders_per_line from data
 where lines in ('1, '2', '3')
 group by order_id, lines
 )
 group by lines

My query is wrong, as I only want 2 columns, and also creating a sequence of numbers ranging from 1 to max(lines per order) is also wrong.

Any suggestions?

Thanks in advance!

JokoSumanto

Try this:

Select Count(*) as Orders, Lines from (
    Select order_id, Count(*) as Lines from data group by order_id
)query group by Lines

For exmaple, look at this sqlfiddle

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

SQL - Count Total Number of Orders By Salesperson

From Dev

SQL Create view to count total number of orders

From Dev

SQL - Count Total Number of Orders By Salesperson

From Dev

Find word in line and count number of line

From Dev

How to count the number of characters on each line in vim?

From Dev

How to count the number of appearances of a word in each line

From Dev

How to count the number of a specific character in each line?

From Dev

Find first occurence of a number in each line of a file

From Dev

find the number of orders per customer

From Dev

Count number of NULL values in each column in SQL

From Dev

Count number of NULL values in each column in SQL

From Dev

SQL find total count of each type in a column

From Dev

Select orders with certain item and certain line count

From Dev

Find the number of employees in each department - SQL Oracle

From Dev

Replace user names by numbers and count number of orders

From Dev

Count the number of Purchase Orders that occur within a Range

From Dev

Replace user names by numbers and count number of orders

From Dev

Count number of times each line repeats itself, In case Insensitive manner

From Dev

SQL Query: How do i get list of address and number of orders to each address

From Dev

How to get the number of orders for each category?

From Dev

awk to find number of columns for each line and exit if more than required

From Dev

How to find number of each line starting with specific parameter

From Dev

Count characters for each line

From Dev

SQL - How to count records for each status in one line per day?

From Dev

Count number of people in each country in SQL server 2008

From Dev

SQL query on how to Count the number of comments each post have

From Dev

Count number of words from file, separated by each line, and return highest number

From Dev

Count each product orders in product listing with one query

From Dev

SQL : How to find number of occurrences without using HAVING or COUNT?

Related Related

  1. 1

    SQL - Count Total Number of Orders By Salesperson

  2. 2

    SQL Create view to count total number of orders

  3. 3

    SQL - Count Total Number of Orders By Salesperson

  4. 4

    Find word in line and count number of line

  5. 5

    How to count the number of characters on each line in vim?

  6. 6

    How to count the number of appearances of a word in each line

  7. 7

    How to count the number of a specific character in each line?

  8. 8

    Find first occurence of a number in each line of a file

  9. 9

    find the number of orders per customer

  10. 10

    Count number of NULL values in each column in SQL

  11. 11

    Count number of NULL values in each column in SQL

  12. 12

    SQL find total count of each type in a column

  13. 13

    Select orders with certain item and certain line count

  14. 14

    Find the number of employees in each department - SQL Oracle

  15. 15

    Replace user names by numbers and count number of orders

  16. 16

    Count the number of Purchase Orders that occur within a Range

  17. 17

    Replace user names by numbers and count number of orders

  18. 18

    Count number of times each line repeats itself, In case Insensitive manner

  19. 19

    SQL Query: How do i get list of address and number of orders to each address

  20. 20

    How to get the number of orders for each category?

  21. 21

    awk to find number of columns for each line and exit if more than required

  22. 22

    How to find number of each line starting with specific parameter

  23. 23

    Count characters for each line

  24. 24

    SQL - How to count records for each status in one line per day?

  25. 25

    Count number of people in each country in SQL server 2008

  26. 26

    SQL query on how to Count the number of comments each post have

  27. 27

    Count number of words from file, separated by each line, and return highest number

  28. 28

    Count each product orders in product listing with one query

  29. 29

    SQL : How to find number of occurrences without using HAVING or COUNT?

HotTag

Archive