CODEWITHSUNDEEP
sqlsql-server
Desmond
Desmond

Reputation: 5011

SQL Query question

i have this query to show

there are 2 tables, i will get the number of renovation from the table renovation while the customer-id and name is from table 1, customer.

enter image description here

SELECT c.[Customer-ID], c.name, COUNT(*)"Number of Renovation"
FROM CUSTOMER c, RENOVATION r
WHERE c.[Customer-ID] = r.[Customer-ID]
GROUP BY c.[Customer-ID], c.name
HAVING Count(*) in 
(SELECT COUNT(*) FROM RENOVATION GROUP BY [Customer-ID])
ORDER BY c.[customer-id]

this is not the right way for me to do the query, anybody know how to shorten the query ? or the other ways of doing it ? though it still find the answer. i'm learning SQL server by the way.

Upvotes: 2

Views: 467

Answers (4)

ripunj
ripunj

Reputation: 1

Try following query,

select table1.id,table1.name,renovation .mobile_no from table1,renovation where table1.id=renovation.id

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57073

I wouldn't recommend the HAVING keyword for newbies, it is essentially for legacy purposes.

The following is more verbose but could be easier to understand and therefore maintain (I've used a CTE for the table CUSTOMER_RENOVATION_TALLIES but it could be a VIEW :

WITH CUSTOMER_RENOVATION_TALLIES ("Customer-ID", Tally)
     AS
     (
      SELECT [Customer-ID], COUNT(*) AS Tally
        FROM RENOVATION
       GROUP 
          BY [Customer-ID]
     )
SELECT c."Customer-ID", c.name, r.Tally
  FROM CUSTOMER AS c
       INNER JOIN CUSTOMER_RENOVATION_TALLIES AS r
          ON c."Customer-ID" = r."Customer-ID";

If you want to include a tally of zero for customers without renovations then UINON this set to the above resultset e.g. 

WITH CUSTOMER_RENOVATION_TALLIES ("Customer-ID", Tally)
     AS
     (
      SELECT [Customer-ID], COUNT(*) AS Tally
        FROM RENOVATION
       GROUP 
          BY [Customer-ID]
     )
SELECT c."Customer-ID", c.name, r.Tally
  FROM CUSTOMER AS c
       INNER JOIN CUSTOMER_RENOVATION_TALLIES AS r
          ON c."Customer-ID" = r."Customer-ID"
UNION 
SELECT c."Customer-ID", c.name, 0 AS Tally
  FROM CUSTOMER AS c
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM CUSTOMER_RENOVATION_TALLIES AS r
                    WHERE c."Customer-ID" = r."Customer-ID"
                  );

Upvotes: 0

Boris Nikolaevich
Boris Nikolaevich

Reputation: 1461

The HAVING clause does not seem to belong here. HAVING is intended to filter out resulting groups based on the aggregate result. For example, you could use the HAVING clause to exclude records that do not have any renovations:

SELECT c.[Customer-ID], c.name, COUNT(*) AS [Number of Renovations]
FROM dbo.CUSTOMER c
INNER JOIN dbo.RENOVATION r ON c.[Customer-ID] = r.[Customer-ID]
GROUP BY c.[Customer-ID], c.name
HAVING COUNT(*) > 0

Upvotes: 3

marc_s
marc_s

Reputation: 755321

OK, so you want customers and the renovations they have - why not just use :

SELECT c.[Customer-ID], c.name, COUNT(*) AS 'Number of Renovations'
FROM dbo.CUSTOMER c
INNER JOIN dbo.RENOVATION r ON c.[Customer-ID] = r.[Customer-ID]
GROUP BY c.[Customer-ID], c.name

I don't quite understand what you're trying to achieve with the HAVING COUNT(*) IN...... part of your query......

If you want to have all customers that have at least one renovation - try this:

SELECT c.[Customer-ID], c.name, COUNT(*) AS 'Number of Renovations'
FROM dbo.CUSTOMER c
INNER JOIN dbo.RENOVATION r ON c.[Customer-ID] = r.[Customer-ID]
GROUP BY c.[Customer-ID], c.name
HAVING COUNT(*) > 0

Upvotes: 5

Related Questions