Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL COUNT statement using JOIN between TBLS

Posted on 2009-03-31
8
Medium Priority
?
543 Views
Last Modified: 2012-05-06
SQL 1:

Below is the example data in two tables, one called statistic and one called address. I need to write an SQL count statement that gives me the results below these two tables which is basically top visited cities in order of popularity.

table called 'statistic'

statisticid | addressid | listingid
--------------------------------------
    1       |       1     |      9
--------------------------------------
    2       |       3      |      2
--------------------------------------
    3       |       1      |      10
--------------------------------------
    4       |       3      |      4
--------------------------------------
    5       |       2      |      6
--------------------------------------
    6       |       1      |      1
--------------------------------------
    7       |       1      |      7
--------------------------------------

table called 'address'

addressid | city |
-------------------------
      1      |      LONDON
-------------------------
      2      |      GLASGOW
-------------------------
      3      |      BELFAST
-------------------------

DESIRED RESULTS SQL.1

location | hits      |
-------------------------
LONDON   |   4
-------------------------
BELFAST   |   2
-------------------------
GLASGOW   |  1
-------------------------

SQL 2:

Then it gets a little trickier, using the statistic table above again and the table below called listing, I need to find the most visited category.

table called 'listing'

listingid  |  category
-------------------------
1    |    beer
-------------------------
2    |    beer
-------------------------
3    |    cider
-------------------------
4    |    spirits
-------------------------
5    |    water
-------------------------
6    |    juice
-------------------------
7    |    wine
-------------------------
8    |    coffee
-------------------------
9    |    beer
-------------------------
10    |    spirits
-------------------------

DESIRED RESULTS SQL.2

category    |      hits
-------------------------
beer    |      3
-------------------------
spirits    |      2
-------------------------
juice    |      1
-------------------------
wine    |      1
-------------------------

It's all probably very simple but I'm new to count statements in SQL. Please note that I'm running SQL Server 2005 so it must be SQL that is compatible with this.

Thanks!
0
Comment
Question by:tobzzz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24028905
1)

SELECT a.city, COUNT(*) AS Hits
FROM address a INNER JOIN
      statistic s ON a.addressid = s.addressid
GROUP BY a.city

2)

SELECT l.category, COUNT(*) AS Hits
FROM listing l
GROUP BY l.category
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24028913
To get them to sort most to least, add the following clause at the end of each statement:

ORDER BY COUNT(*) DESC

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24028928
1.  

select city, count(*) as hits
from statistics s join address a on s.addressid = a.addressid
group by city

2.

select category, count(*) as hits
from listing
group by category
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Author Comment

by:tobzzz
ID: 24029051
the solution to SQL 1 is perfect but i messed up the SQL 2 question, sorry! I just need you to answer this and i'll send you the points! thanks guys...

SQL 2.
The statistic table has thousands of entries, every time a listing is viewed it is stored as a new row in the statistic table. So one listingid may occur on numerous rows, it may only have been viewed/stored one time, it's irrelevant. The point is, each listingid has a category assigned to it. I need to relate/join the statistic table to the listing table and I want to see which category has seen the most hits.

I hope that's clearer. Sorry again guys, you're answers were very fast and perfectly what i needed in SQL.1.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24030364
tobzzz,

Sorry, still not clear on the second item.  Can you work up an example, please?  :)

Regards,

Patrick
0
 
LVL 11

Author Comment

by:tobzzz
ID: 24031068
OK, let me try again...

'statistic' table is populated by page views of an advert on a website. The table could look like this:

statisticid | advertid
----------------
1       |        9
----------------
2       |        2
----------------
3       |        10
----------------
4       |        4
----------------
5       |        6
----------------
6       |        1
----------------
7       |        7
----------------

'listing' table is the table of adverts, each row is unique with whatever the advertiser is selling but contains a column called 'category' which is one of five categories. So a the table could look like this:

advertid  |  category
-------------------------
1    |    beer
-------------------------
2    |    beer
-------------------------
3    |    cider
-------------------------
4    |    spirits
-------------------------
5    |    water
-------------------------
6    |    juice
-------------------------
7    |    wine
-------------------------
8    |    coffee
-------------------------
9    |    beer
-------------------------
10   |    spirits
-------------------------

We can see by eye from this table that 3 advertisers are selling beer. 2 are selling spirits. 1 is selling cider, 1 is selling water, 1 is selling juice and 1 is selling coffee but this is NOT what I am wanting to know.

What I want to know is, by relating the two tables together, what category has been VIEWED the most?

The desired result would be:
1st most viewed = beer ... 3 hits (in statistic number 1, 2 and 6)
2nd most viewed = spirits ... 2 hits (in statistic number 3 and 4)
joint 3rd most viewed = juice ... 1 hit (in statistic number 5)
joint 3rd most viewed = wine ... 1 hit (in statistic number 7)

cider, water and coffee saw NO views in the statistic table so they are not in the results.

I hope that makes sense! :@) Thanks Patrick
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24031397
OK, same way as for the first example:


SELECT l.category, COUNT(*) AS Hits
FROM listing l INNER JOIN
      statistic s ON l.avertid = s.advertid
GROUP BY l.category
ORDER BY COUNT(*) DESC


To include the ones with no hits...

SELECT l.category, COUNT(*) AS Hits
FROM listing l LEFT JOIN
      statistic s ON l.avertid = s.advertid
GROUP BY l.category
ORDER BY COUNT(*) DESC
0
 
LVL 11

Author Closing Comment

by:tobzzz
ID: 31564824
Perfect. I fully understand COUNT as I need it for my database. I added another 2 joins and a where statement using your principles and got exactly the result I needed. Thanks Patrick.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question