2

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.

Improve this question
2
  • i wanna list the customers and the number of renovation they have. i wanna use subquery but not sure if i'm doing the right thing. Commented Aug 22, 2011 at 19:18
  • the Having clause works similarly to the Where clause except it's attached to the Group By clause. Since there is no limiting condition in the requirements, there is no need for a Having clause. Commented Aug 22, 2011 at 21:55

4 Answers 4

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

i see, that is how short is can be, it works !!!! thanks Marc_s. i will thinking that the HAVING clause is used to find if there is a value before i did the next step.
3

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
Improve this answer

1 Comment

Since @marc_s posted the same answer above — and nearly identical code — he should get the votes.
0

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"
                  );
Improve this answer

4 Comments

I disagree with your statement that "it is essentially for legacy purposes" and even more with the article you linked to which says that "HAVING means exactly the same as WHERE, but has different rules concerning what can be written in the condition that follows it and in the SELECT clause that physically precedes it." There is a very important difference between WHERE and HAVING that makes HAVING useful for more than just "legacy purposes": WHERE filters records before grouping is applied, and HAVING applies filtering on the aggregates after grouping is applied.
The fact that they do different things necessitates the different terminology; how much more confusing would it be if both criteria operators were called WHERE?
@Boris Nikolaevich: it could be you missed the main point i.e. quote "if only the System R team had realized that their language was relationally incomplete without support for derived tables in the FROM clause, then they (and after them, Larry Ellison with his Oracle that brought SQL to the unsuspecting public at large) might have included such support and thus allowed their users... not to have to learn HAVING." Of course, it could be that you understand fully yet hold a different opinion, which is fine by me :)
@Boris Nikolaevich: BTW no one, AFAIK, is proposing that WHERE clause should be "filtering on the aggregates" within the same scope. The point is the HAVING came about because SQL didn't allow nested queries and HAVING couldn't (and never can) be subsequently removed due to compatibility issues.
0

Try following query,

select table1.id,table1.name,renovation .mobile_no from table1,renovation where table1.id=renovation.id
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.