CODEWITHSUNDEEP
sql-server
Wild Goat
Wild Goat

Reputation: 3579

SQL Server query to non normalized table

I have an unnormalized table of customer orders. I want count how many products are sold and display it in table by type.

OnlineSalesKey  SalesOrderNumber  ProductKey
--------------------------------------------
     1              20121018         778
     2              20121018         774
     3              20121018         665
     4              20121019         772
     5              20121019         778
     9              20121019         434
    10              20121019         956
    11              20121020         772
    12              20121020         965
    15              20121020         665
    16              20121020         778
    17              20121021         665

My query:

SELECT 
    s.ProductKey, COUNT (*) As Purchased 
FROM
    Sales s
GROUP BY 
    s.ProductKey

Question #1.

That query does a job. But now I want display and take into account only those orders where more than one item is purchased. Not sure how do i do that in one query. Any ideas?

Question #2

Is it possible to normalize results and get back data separated by semi column? 

20121018 | 778; 774; 665

Thanks!

Upvotes: 0

Views: 1002

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

Q1: Look at HAVING clause

display and take into account only those orders... 

SELECT s.SalesOrderNumber, COUNT (*) As Purchased 
FROM Sales s
GROUP BY s.SalesOrderNumber
HAVING COUNT(*) > 1

So we group by the orders, apply the condition in HAVING, then display the SalesOrderNumber in the SELECT clause.

Q2: Look at several group concatenation techniques.

MySQL: 

SELECT s.SalesOrderNumber, GROUP_CONCAT(DISTINCT ProductKey
                                      ORDER BY ProductKey SEPARATOR '; ')
FROM Sales s
GROUP BY s.SalesOrderNumber

SQL Server: See this answer to a duplicate question. Basically, using FOR XML.

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 51000

You don't say which SQL database you're using, and there will be different, more-or-less efficient answers for each database. (Sorry, just noticed MSSQL is in the question title.)

Here's a solution that will work in all or most databases:

SELECT s.ProductKey, COUNT (*) As Purchased 
FROM Sales s
WHERE SalesOrderNum IN 
 (SELECT SalesOrderNum FROM Sales GROUP BY SalesOrderNum HAVING COUNT(*) > 1)
GROUP BY s.ProductKey

This is not the most efficient, but should work across the most products.

Also, please note that you're using the terms normalized and unnormalized in reverse. The table you have is normalized, the results you want are de-normalized.

There is no standard SQL statement to get the de-normalized results you want using SQL alone, but some databases (MySQL and SQLite) provide the group_concat function to do just this.

Upvotes: 4

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55509

SELECT s.ProductKey, COUNT (*) As Purchased 
FROM
Sales s
GROUP BY s.ProductKey
having count(*) > 1

EDIT -

Answer 1 - To display products for which orders had more than one product purchased -

SELECT s.ProductKey, COUNT (*) As Purchased 
FROM Sales s
WHERE SalesOrderNum in
(
select SalesOrderNum from Sales 
group by SalesOrderNum having count(*) > 1
)
GROUP BY s.ProductKey

Upvotes: 1

Olaf Dietsche
Olaf Dietsche

Reputation: 74078

1) Try this one:

SELECT s.ProductKey, COUNT (*) As Purchased 
FROM
Sales s
GROUP BY s.ProductKey
HAVING COUNT(*) > 1

Upvotes: 1

Related Questions