CODEWITHSUNDEEP
sqlsql-server
Ben Feil
Ben Feil

Reputation: 13

SQL Combining 2 queries into 1

Here is my question, I am trying to find the employee that has made the most sales in the state that has the most sales. I have written separate queries to find first the state with the most sales, then to find the employee with the most sales in that state.

My problem is that I can only have one query, so I must combine the two using a JOIN or subquery, but I am unable to do that because my first query is returning 2 separate columns. How might I combine these queries?

SELECT      TOP 1 
            StateProvince AS "Most Popular State",
            COUNT (*) AS "Number Sold"
FROM        CUSTOMER JOIN INVOICE ON CustomerID = FK_CustomerID

GROUP BY    StateProvince
ORDER BY    "Number Sold" DESC



SELECT      TOP 1
            EMPLOYEE.Name,
            EmpID,
            COUNT(InvoiceNbr) AS "Number Sold"
FROM        EMPLOYEE JOIN INVOICE ON EmpID = FK_EmpID
JOIN        CUSTOMER ON FK_CustomerID = CustomerID
WHERE       StateProvince = --Most popular state found in 1st query
GROUP BY    EMPLOYEE.Name, EmpID
ORDER BY    "Number Sold" DESC

Upvotes: 1

Views: 47

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

should be this 

SELECT      TOP 1
            EMPLOYEE.Name,
            EmpID,
            COUNT(InvoiceNbr) AS "Number Sold By EMP", 
            StateProvince AS "Most Popular State",
            COUNT (*) AS "Number Sold For STATE"
FROM        EMPLOYEE JOIN INVOICE ON EmpID = FK_EmpID
JOIN        CUSTOMER ON FK_CustomerID = CustomerID
GROUP BY    EMPLOYEE.Name, EmpID
ORDER BY    "Number Sold By EMP" DESC

Upvotes: 1

Related Questions