CODEWITHSUNDEEP
mysql
user15063
user15063

Reputation:

Keep all records in "WHERE IN()" clause, even if they are not found

I have the following mysql query:

SELECT id, sum(views) as total_views
FROM table
WHERE id IN (1,2,3)
GROUP BY id
ORDER BY total_views ASC

If only id 1,3 are found in the database, i still want id 2 to appear, with total_views being set to 0.

Is there any way to do that? This cannot use any other table.

Upvotes: 5

Views: 4630

Answers (6)

batwad
batwad

Reputation: 3665

Does it have to be rows or could you pivot the data to give you one row and a column for every id?

SELECT
  SUM(IF (id=1, views, 0)) views_1,
  SUM(IF (id=2, views, 0)) views_2,
  SUM(IF (id=3, views, 0)) views_3
FROM table

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

try this

    SELECT id 
                (CASE 1
                    IF EXISTS THEN  views = mytable.views END
                    IF NOT EXIST THEN views = 0 END
                CASE 2
                    IF EXISTS THEN  views = mytable.views END
                    IF NOT EXIST THEN views = 0 END
                CASE 3
                    IF EXISTS THEN  views = mytable.views END
                    IF NOT EXIST THEN views = 0 END), sum(views) as total_views
    FROM mytable
    WHERE id IN (1,2,3)
    GROUP BY id
    ORDER BY total_views ASC

Upvotes: 0

svidgen
svidgen

Reputation: 14282

Here's an alternative to Micheal's solution (not a bad solution, mind you -- even with "a lot" of ID's), so long as you're not querying against a cluster.

create temporary table __ids (
  id int unsigned primary key
) engine=MEMORY;

insert into __ids (id) values
  (1),
  (2),
  (3)
;

SELECT table.id, sum(views) as total_views
FROM __ids left join table using (id)
GROUP BY table.id
ORDER BY total_views ASC

And if your query becomes complex, I could even conceive of it running more efficiently this way. But, if I were you, I'd benchmark this option with Michael's ad-hoc UNION'ed table option using real data.

Upvotes: 3

Nilam Doctor
Nilam Doctor

Reputation: 491

Check this fiddle... http://www.sqlfiddle.com/#!2/a9392/3

Select B.ID, sum(A.views) sum from tableB B
 left outer join tableA A
 on B.ID = A.ID
group by A.ID

also check http://www.sqlfiddle.com/#!2/a1bb7/1

Upvotes: 0

Rob Starling
Rob Starling

Reputation: 3908

in @Michael's answer, if you do have a table with the ids you care about, you can use it as "ids" in place of Michael's in-line data.

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

This query hard-codes the list of possible IDs using a sub-query consisting of unions... it then left joins this set of ids to the table containing the information to be counted.

This will preserve an ID in your results even if there are no occurrences:

SELECT ids.id, sum(views) as total_views
FROM (
    SELECT 1 AS ID 
    UNION ALL SELECT 2 AS ID 
    UNION ALL SELECT 3 AS ID 
) ids 
LEFT JOIN table
    ON table.ID = ids.ID
GROUP BY ids.id
ORDER BY total_views ASC

Alternately, if you had a numbers table, you could do the following query:

SELECT numbers.number, sum(views) as total_views
FROM
    numbers
    LEFT JOIN table
        ON table.ID = ids.ID
WHERE numbers.number IN (1, 2, 3)
GROUP BY numbers.number
ORDER BY total_views ASC

Upvotes: 7

Related Questions