SQL: How to get the different value from one column/type?

sql select unique values from multiple columns
select distinct on one column, with multiple columns returned sql
sql select distinct multiple columns
sql distinct on one column
sql select distinct values and count of each
sql distinct vs unique
sql count(distinct multiple columns)
mysql distinct multiple columns

I am trying to sum the total number from a name table below:

id  type  num
2   0     90
24  1     38
2   1     878
9   0     8763
9   2     76
9   1     374

There are three types in the type column, I would like to find all ids which their type = 0 and 1. for example, in the table, the id = 2 and id = 9 have the type = 0 and type = 1. So will pick these data and add them together like below:

       type: num
id 2 - 0:90
       1:878

id 9 - 0:8763
       1: 374

sum = 90 + 878 + 8764 + 374 = 10,106

I did the query like this:

SELECT SUM(num) FROM  table WHERE type = 0 AND type = 1;

ut there is nothing to show. It workq if I change the query to the type = 0 OR type = 1, but I do not think its right. How to sum num where type = 0 and type = 1?

I am very confused, I am not sure am I explain clearly, if you can give some helps it will be very appreciated.

With this query:

  select id from tablename
  where type in (0, 1)
  group by id
  having min(type) = 0 and max(type) = 1

you get all the ids that have both types 0 and 1. So you join it to the table to get the total you want:

select sum(t.num) total 
from tablename t inner join (
  select id from tablename
  where type in (0, 1)
  group by id
  having min(type) = 0 and max(type) = 1
) g on g.id = t.id
where t.type in (0, 1)

See the demo Or without join:

select sum(t.num) total from (
  select id, sum(num) num from tablename
  where type in (0, 1)
  group by id
  having min(type) = 0 and max(type) = 1
) t

See the demo

SQL SELECT DISTINCT Statement, Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT Syntax. Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server To get the Table in information in SQL Server, we can use below Query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Users' ORDER BY ORDINAL_POSITION

This should do the trick:

select
  sum(a.num + b.num)
from (
  select id, num from table where type = 0
) a
join (
  select id, num from table where type = 1
) b on a.id = b.id

SQL Server SELECT DISTINCT, The query uses the combination of values in all specified columns in the SELECT list to evaluate the uniqueness. If you apply the DISTINCT clause to a column� Returns a datetime value containing the date and time of the computer on which the instance of SQL Server runs. The returned value does not include the time zone offset. datetime: Nondeterministic: GETDATE: GETDATE ( ) Returns a datetime value containing the date and time of the computer on which the instance of SQL Server runs. The returned

If I understand the question well the answer is quite easy:

SELECT SUM(num)
FROM name
WHERE type = 0 OR type = 1

You want to sum all num values from the rows where type = 0 or type = 1. That's exactly what the above SQL query expresses. Which you've actually come to as well.

Is it that what you need @lily?

Converting Between Data Types with SQL Server Functions , One is an SMALLINT value, which has no decimal places, and the other is MONEY, which does. SELECT P.Name, I.Quantity, P.StandardCost, I.Quantity * P. In this particular case, the variable value is EMPTY, i.e., NULL. USING SELECT. Just like SET, we can also use SELECT to assign values to the variables, post declaring a variable using DECLARE. Below are different ways to assign a value using SELECT: Example: Assigning a value to a variable using SELECT . Syntax:

SQL Server Data Types Tutorial, In SQL the columns of a table are defined to store a specific kind of value such as Before we get into the data types used in SQL server, I think it is important for� Yes, but it'll be different depending on what type of RDBMS you're using - SQL is a language, not the database product, and this question relies on the specific product. You'll be able to find this sort of information in the INFORMATION_SCHEMA.COLUMNS table - if your RDBMS has it. – Bridge Nov 15 '12 at 20:27

How to Select the Right Data Types, You can grant a user table and column permissions in MySQL with GRANT statements. How to Find Duplicate Values in a SQL Table. Learn how to check a � There is no way to have different data types within one column. But you can simulate it by converting all the values to varchar like below Value, Year FROM

MySQL 8.0 Reference Manual :: 11.3.6 The SET Type, A SET column can have a maximum of 64 distinct members. Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled. SQL developers have to decide what types of data will be stored inside each and every table column when creating a SQL table. The data type is a label and a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

Comments
  • where type = 0 and type = 1. Do you think this will give you any result? The two condition together will never be true. A record can't be both type 0 and 1.